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's WHERE clause. In general, subquery unnesting 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 non-correlated 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.
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 unnesting 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.