Refresher

A subquery is a SELECT statement with another SELECT inside it. It looks like this:

 SELECT ...         /* outer or parent query */   (SELECT ...)     /* subquery, inner query or subselect */ ... 

There are three possible plans to process a subquery:

• flattened . Transform the query to a join, then process as a join.

• out-to-in. For each row in the outer query, look up in the inner query.

• in-to-out. For each row in the inner query, look up in the outer query.

When processing is out-to-in, the outer query is the driver. When processing is in-to-out, the inner query is the driver.

Table 6-1 shows the SQL Standard requirements and the level of support the Big Eight have for subqueries.

Notes on Table 6-1:

• Basic Support column

  This column is "Yes" if the DBMS fully supports the [NOT] IN , [NOT] EXISTS , <comparison operator> ANY , and <comparison operator> ALL predicates, and correctly handles subquery situations where the number of rows is zero.

Table 6-1. ANSI/DBMS Subquery Support

	Basic Support	Row Subquery	Table Subquery	Max Depth	Allow UNION	Types Converted
ANSI SQL	Yes	Yes	Yes	N/S	Yes	Yes
IBM	Yes	Yes	Yes	22	Yes	Yes
Informix	Yes	No	No	23	No	Yes
Ingres	Yes	No	No	11	No	Yes
InterBase	Yes	No	No	>=32	No	Yes
Microsoft	Yes	No	Yes	>=32	Yes	Yes
MySQL	No	No	No	N/A	N/A	Yes
Oracle	Yes	No	No	>=32	Yes	Yes
Sybase	Yes	No	No	16	No	Yes

• Row Subquery column

  This column is "Yes" if the DBMS supports row subqueries, for example:

   SELECT * FROM Table1   WHERE (column1, column2) =     (SELECT column1, column2 FROM Table2) 

• Table Subquery column

  This column is "Yes" if the DBMS supports table subqueries, for example:

   SELECT *   FROM (SELECT * FROM Table1) AS TableX 

• Max Depth column

  Shows the maximum number of subquery levels supported.

• Allow UNION column

  This column is "Yes" if the DBMS allows UNION in a subquery.

• Types Converted column

  This column is "Yes" if the DBMS automatically casts similar data types during comparisons.