ASA SQL User's Guide
  Query Optimization and Execution
    Semantic query transformations
      Types of semantic transformations

Subquery unnesting

You may 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 Adaptive Server Anywhere can take better advantage of highly selective conditions in a subquery's WHERE clause.

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, Adaptive Server Anywhere recognizes that it can convert it to an inner join.

SELECT s.*
FROM sales_order_items s
WHERE EXISTS
    (   SELECT *
      FROM product p
      WHERE s.prod_id = p.id
         AND p.id = 300 AND p.quantity > 300)

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

SELECT s.*
FROM product p JOIN sales_order_items s
   ON p.id = s.prod_id
WHERE p.id = 300 AND p.quantity > 20

p<seq> JNL s<ky_prod_id>

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

SELECT p.*
FROM product p
WHERE EXISTS
    (   SELECT *
      FROM sales_order_items s
      WHERE s.prod_id = p.id
         AND s.id = 2001)

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

SELECT DISTINCT p.*
FROM product p JOIN sales_order_items s
   ON p.id = s.prod_id
WHERE s.id = 2001

DistI[ s<id_fk> JNL p<product> ]

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

SELECT *
FROM product p
WHERE p.id =
    (   SELECT s.prod_id
      FROM sales_order_items s
      WHERE s.id = 2001
         AND s.line_id = 1 )

Adaptive Server Anywhere rewrites this query as follows.

SELECT p.*
FROM product p, sales_order_items s
WHERE p.id = s.prod_id
   AND s.id = 2001
   AND s.line_id = 1

p<seq> JNL s<sales_order_items>

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.