User's Guide
   PART 1. Working with Databases
     CHAPTER 7. 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 product ID's and line ID's of those items that were shipped 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
2002	2
...	...

Explanation of the nested subqueries 

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

SELECT code 
FROM fin_code 
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 order_date 
FROM sales_order 
WHERE fin_code_id IN (subquery)

• Finally, the outermost query finds the product ID's and line ID's of the items that were shipped on one of the dates found in the 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.