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