Correlated Subqueries

A subquery that references one or more columns from its containing SQL statement is called a correlated subquery. 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 table.