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 the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.