Interpret Correlated subqueries in Oracle

    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 )

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)
);

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');