Mastering Oracle SQL

Correlated Subqueries

A subquery that references one or more columns from its containing SQL statement is called a correlated subquery. Unlike noncorrelated subqueries, which are executed exactly once prior to execution of the containing statement, a correlated subquery is executed once for each candidate row in the intermediate result set of the containing query. For example, consider the following query, which locates all parts supplied by Acme Industries that have been purchased ten or more times since December:

SELECT p.part_nbr, p.name
FROM supplier s, part p
WHERE s.name = 'Acme Industries' 
  AND s.supplier_id = p.supplier_id
  AND 10 <= 
   (SELECT COUNT(*) 
    FROM cust_order co, line_item li
    WHERE li.part_nbr = p.part_nbr 
      AND li.order_nbr = co.order_nbr
      AND co.order_dt >= TO_DATE('01-DEC-2001','DD-MON-YYYY'));

The reference to p.part_nbr is what makes the subquery correlated; values for p.part_nbr must be supplied by the containing query before the subquery can execute. If there are 10,000 parts in the part table, but only 100 are supplied by Acme Industries, the subquery will be executed once for each of the 100 rows in the intermediate result set created by joining the part and supplier tables.^[9]

Correlated subqueries are often used to test whether relationships exist without regard to cardinality. We might, for example, want to find all parts that have shipped at least once in 2002. The EXISTS operator is used for these types of queries, as illustrated by the following query: ...