Course Project: Interpret correlated subqueries in Oracle
Left Column
Matching Right column
SELECT LASTNAME
FROM CUSTOMER C
WHERE EXISTS (SELECT 'X' FROM CUSTOMER_SALE D
WHERE TOTAL_SALE_AMOUNT < 40 AND C.CUST_ID = D.CUST_ID )
LASTNAME -------------- Lee
SELECT LASTNAME
FROM CUSTOMER C
WHERE 2 = (SELECT COUNT(*) FROM CUSTOMER_SALE D WHERE C.CUST_ID = D.CUST_ID )
LASTNAME ------------- Black Lee
SELECT LASTNAME
FROM CUSTOMER C, CUSTOMER_SALE CS
WHERE CS.CUST_ID = C.CUST_ID AND CS.TOTAL_SALE_AMOUNT = (SELECT TOTAL_ITEM_AMOUNT FROM CUSTOMER_SALE SALE WHERE CS.SALES_ID =
SALE.SALES_ID )
LASTNAME --------------- Redding
SELECT LASTNAME
FROM CUSTOMER C[[delete break]]
WHERE NOT EXISTS (SELECT 'X' FROM CUSTOMER_SALE SALE WHERE C.CUST_ID = SALE.CUST_ID)
LASTNAME ------------- Laurel
Correlated Subqueries
Is there another way to perform multitable joins? Recall that a where clause can contain a subquery select. Subquery selects can be nested, that is, a where clause in a subquery also can contain a where clause with a subquery, which can contain a where clause with a subquery. The following shows three selects, each connected to another through a where clause:
select distinct C.ParentCategory, C.SubCategory
from CATEGORY C
where CategoryName in
(select CategoryName from BOOKSHELF
where Title in
(select Title from BOOKSHELF_CHECKOUT)
);
Result for first subquery
This query selects any categories containing books that have been checked out. It does this simply by requesting a book whose title is in the BOOKSHELF table and whose checkout record is in the BOOKSHELF_CHECKOUT table. In a subquery, Oracle assumes the columns to be from the first select statement, the one that contains the subquery in its where clause. This is called a nested subquery, because for every CategoryName in the main (outer) query, the CategoryName may be correlated in the second where clause. Said differently, a subquery may refer to a column in a table used in its main query (the query that has the subquery in its where clause). Consider the following query:
select Title from BOOKSHELF_AUTHOR
where Title in
(select Title from BOOKSHELF
where AuthorName = 'STEPHEN JAY GOULD');
