Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Query Optimization and Execution » Semantic query transformations » Types of semantic transformations

Elimination of unnecessary DISTINCT conditions Next Page

Unnesting 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'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.

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 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.