All of the queries described thus far in this chapter have been two-level queries, involving a main query and a subquery. Just as you can use a subquery inside a main query, you can use a subquery inside another subquery. Here is an example of a request that is naturally represented as a three-level query, with a main query, a subquery, and a sub-subquery:
List the customers whose salespeople are assigned to offices in the Eastern sales region.
SELECT COMPANY
FROM CUSTOMERS
WHERE CUST_REP IN (SELECT EMPL_NUM
FROM SALESREPS
WHERE REP_OFFICE IN (SELECT OFFICE
FROM OFFICES
WHERE REGION = ‘Eastern’))
COMPANY
———-
First Corp.
Smithson Corp.
AAA Investments
JCP Inc.
Chen Associates
QMA Assoc.
Ian & Schmidt
Acme Mfg.
In this example, the innermost subquery:
SELECT OFFICE
FROM OFFICES
WHERE REGION = ‘Eastern’
produces a column containing the office numbers of the offices in the Eastern region. The next subquery:
SELECT EMPL_NUM
FROM SALESREPS
WHERE REP_OFFICE IN (subquery)
produces a column containing the employee numbers of the salespeople who work in one of the selected offices. Finally, the outermost query:
SELECT COMPANY
FROM CUSTOMERS
WHERE CUST_REP IN (subquery)
finds the customers whose salespeople have one of the selected employee numbers.
The same technique used in this three-level query can be used to build queries with four or more levels. The ANSI/ISO SQL standard does not specify a maximum number of nesting levels, but in practice, a query becomes much more time-consuming as the
number of levels increases. The query also becomes more difficult to read, understand, and maintain when it involves more than one or two levels of subqueries. Many SQL implementations restrict the number of subquery levels to a relatively small number.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.