Referencing Outside with Correlated Subqueries

The subqueries you have written so far are self-contained. None of them has used a reference from outside the subquery. Correlated subqueries enable you to use an outside reference with some strange and wonderful results. Look at the following query:

						SELECT *
						FROM ORDERS O
						WHERE 'ROAD BIKE' =
						(SELECT DESCRIPTION
						FROM PART P
						WHERE P.PARTNUM = O.PARTNUM)

ORDEREDON   NAME           PARTNUM    QUANTITY REMARKS
=========== ========== =========== =========== ==========

19-MAY-1996 TRUE WHEEL          76           3 PAID
17-JAN-1996 BIKE SPEC           76          11 PAID
17-JAN-1996 LE SHOPPE           76           5 PAID
 1-JUL-1996 AAA BIKE            76           4 PAID
11-JUL-1996 JACKS BIKE          76          14 PAID

This query actually resembles the following join:

						SELECT O.ORDEREDON, O.NAME,
						O.PARTNUM, ...

Get Sams Teach Yourself SQL in 21 Days, Fourth Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.