Sams Teach Yourself SQL in 21 Days, Fourth Edition

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, ...