Optimization of correlated SQL queries in a relational database management system

摘要

A relational database management system (RDBMS) efficiently evaluates correlated subqueries by decorrelating them and taking care of the so-called SQL count bug and yet avoid using the expensive outer join operation. When there is no tuple match from a correlated subquery, the RDBMS query processor returns a tuple of null(s) from a scalar derived table, and then uses COALESCE function to generate a proper count value of zero from the null. The correlation level remains one. The query processor also performs a pass-through optimization to eliminate a join operation for floating SELECT operation by removing a join operation involving the magic operation, so that the correlation bindings are received from the correlation source table rather than the magic operation.