ASA SQL User's Guide
  Query Optimization and Execution
    How the optimizer works
      Underlying assumptions

Rewriting subqueries as EXISTS predicates

The assumptions which underlie the design of Adaptive Server Anywhere require that it conserves memory and that by default it returns the first few results of a cursor as quickly as possible. In keeping with these objectives, Adaptive Server Anywhere rewrites all set-operation subqueries, such as IN, ANY, or SOME predicates, as EXISTS predicates. By doing so, Adaptive Server Anywhere avoids creating unnecessary work tables and may more easily identify a suitable index through which to access a table.

Non-correlated subqueries are subqueries that contain no explicit reference to the table or tables contained in the rest higher-level portions of the query.

The following is an ordinary query that contains a non-correlated subquery. It selects information about all the customers who did not place an order on January 1, 2001.

Non-correlated subquery 

SELECT *
FROM customer c
WHERE c.id NOT IN
   (   SELECT o.cust_id
      FROM sales_order o
      WHERE o.order_date = '2001-01-01' )

One possible way to evaluate this query is to first read the sales_order table and create a work table of all the customers who placed orders on January 1, 2001, then read the customer table and extract one row for each customer listed in the work table.

However, Adaptive Server Anywhere avoids materializing results as work tables. It also gives preference to plans that return the first few rows of a result most quickly. Thus, the optimizer rewrites such queries using EXISTS predicates. In this form, the subquery becomes correlated: the subquery now contains an explicit reference to the id column of the customer table.

Correlated subquery 

SELECT *
FROM customer c
WHERE NOT EXISTS
   (   SELECT *
      FROM sales_order o
      WHERE o.order_date = '2000-01-01'
         AND o.cust_id = c.id )

c<seq> : o<key_so_customer>

This query is semantically equivalent to the one above, but when expressed in this new syntax, two advantages become apparent.

1. The optimizer can choose to use either the index on the cust_id attribute or the order_date attribute of the sales_order table. (However, in the sample database, only the id and cust_id columns are indexed.)

2. The optimizer has the option of choosing to evaluate the subquery without materializing intermediate results as work tables.

Adaptive Server Anywhere can cache the results of this correlated subquery during processing. This strategy lets Adaptive Server Anywhere reuse previously computed results. In the case of query above, caching does not help because customer identification numbers are unique in the customer table.

Further information on subquery caching is located in Subquery and function caching.