Contents Index Subqueries and joins How subqueries work

ASA SQL User's Guide
  Using Subqueries

Nested subqueries


As we have seen, subqueries always 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.

Examples 

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, line_id
FROM sales_order_items
WHERE ship_date = ANY (
   SELECT order_date
   FROM sales_order
   WHERE fin_code_id IN (
      SELECT code
      FROM fin_code
      WHERE (description = 'Fees')))
id line_id
2001 1
2001 2
2001 3
2002 1
... ...
Explanation of the nested subqueries 
SELECT code
FROM fin_code
WHERE (description = 'Fees')
SELECT order_date
FROM sales_order
WHERE fin_code_id IN (subquery)
SELECT id, line_id
FROM sales_order_items
WHERE ship_date = ANY (subquery)

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.


Contents Index Subqueries and joins How subqueries work