Un-nesting subqueries

You can express statements as nested queries, given the convenient syntax provided in the SQL language. However, rewriting nested queries as joins often leads to more efficient execution and more effective optimization, since SQL Anywhere can take better advantage of highly selective conditions in a subquery WHERE clause. In general, subquery un-nesting is always done for correlated subqueries with, at most, one table in the FROM clause, which are used in ANY, ALL, and EXISTS predicates. A uncorrelated subquery, or a subquery with more than one table in the FROM clause, is flattened if it can be decided, based on the query semantics, that the subquery returns at most one row.

Examples

The subquery in the following example can match at most one row for each row in the outer block. Because it can match at most one row, SQL Anywhere recognizes that it can convert it to an inner join.

SELECT s.*
FROM SalesOrderItems s
WHERE EXISTS
    ( SELECT *
      FROM Products p
      WHERE s.ProductID = p.ID
         AND p.ID = 300 AND p.Quantity > 20);

Following conversion, this same statement is expressed internally using join syntax:

SELECT s.*
FROM Products p JOIN SalesOrderItems s
   ON p.ID = s.ProductID
WHERE p.ID = 300 AND p.Quantity > 20;

p<Products> JNL s<FK_ProductID_ID>

Similarly, the following query contains a conjunctive EXISTS predicate in the subquery. This subquery can match more than one row.

SELECT p.*
FROM Products p
WHERE EXISTS
    ( SELECT *
      FROM SalesOrderItems s
      WHERE s.ProductID = p.ID
         AND s.ID = 2001);

SQL Anywhere converts this query to an inner join, with a DISTINCT in the SELECT-list.

SELECT DISTINCT p.*
FROM Products p JOIN SalesOrderItems s
   ON p.ID = s.ProductID
WHERE s.ID = 2001;

Work[ DistH[ s<FK_ID_ID> JNL p<Products> ] ]

SQL Anywhere can also eliminate subqueries in comparisons when the subquery matches at most one row for each row in the outer block. Such is the case in the following query.

SELECT *
FROM Products p
WHERE p.ID =
    ( SELECT s.ProductID
      FROM SalesOrderItems s
      WHERE s.ID = 2001
         AND s.LineID = 1 );

SQL Anywhere rewrites this query as follows:

SELECT p.*
FROM Products p, SalesOrderItems s
WHERE p.ID = s.ProductID
   AND s.ID = 2001
   AND s.LineID = 1;

s<SalesOrderItems> JNL p<Products>

The DUMMY table is treated as a special table when subquery un-nesting rewrite optimizations are performed. Subquery flattening is always done on subqueries of the form SELECT expression FROM DUMMY, even if the subquery is not correlated.

Discuss this page in DocCommentXchange.