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.

   


SQL Performance Tuning
SQL Performance Tuning
ISBN: 0201791692
EAN: 2147483647
Year: 2005
Pages: 125

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net