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

Nested subqueries

As you have seen, subqueries usually appear in the HAVING clause or the WHERE clause of a query. A subquery may itself contain a WHERE clause and/or a HAVING clause, and, consequently, a subquery may appear in another subquery. Subqueries inside other subqueries are called nested subqueries.

List the order IDs and line IDs of those orders shipped on the same day when any item in the fees department was ordered.

SELECT ID, LineID
FROM SalesOrderItems
WHERE ShipDate = ANY (
   SELECT OrderDate
   FROM SalesOrders
   WHERE FinancialCode IN (
      SELECT Code
      FROM FinancialCodes
      WHERE ( Description = 'Fees' ) ) );

• In this example, the innermost subquery produces a column of financial codes whose descriptions are "Fees":

SELECT Code
FROM FinancialCodes
WHERE ( Description = 'Fees' );

• The next subquery finds the order dates of the items whose codes match one of the codes selected in the innermost subquery:

SELECT OrderDate
FROM SalesOrders
WHERE FinancialCode 
IN ( subquery-expression );

• Finally, the outermost query finds the order IDs and line IDs of the orders shipped on one of the dates found in the subquery.

SELECT ID, LineID
FROM SalesOrderItems
WHERE ShipDate = ANY ( subquery-expression );

Nested subqueries can also have more than three levels. Though there is no maximum number of levels, queries with three or more levels take considerably longer to run than do smaller queries.