Listing A
-- Form one using non-correlated subquery
select
  book_title
from
  book
where
  book_key not in (select book_key from sales);
 
 
 
Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=64)           
   1    0   FILTER                                                             
   2    1     TABLE ACCESS (FULL) OF 'BOOK' (Cost=1 Card=1 Bytes=64)           
   3    1     TABLE ACCESS (FULL) OF 'SALES' (Cost=1 Card=5 Bytes=25)          
 
 
 
 
 
 
-- Form two using outer join
select
  book_title
from
  book  b,
  sales  s
where
  b.book_key = s.book_key(+) 
and
  quantity is null;
 
 
 
 
Execution Plan
----------------------------------------------------------          
0   SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=100 Bytes=8200)       
1  0 FILTER            
2  1   FILTER     
3  2     HASH JOIN (OUTER)                                              
4  3      TABLE ACCESS (FULL) OF 'BOOK' (Cost=1 Card=20 Bytes=1280)
5  3      TABLE ACCESS (FULL) OF 'SALES' (Cost=1 Card=100 Bytes=1800)      
 
 
 
 
 
 
-- Form three using correlated subquery
select
  book_title
from
  book
where
  book_title not in (
                select
                distinct
                  book_title
                from
                  book,
                  sales
                where
                  book.book_key = sales.book_key
                and
                  quantity > 0);
 
 
 
 
Execution Plan
----------------------------------------------------------       
0   SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=59)           
1  0  FILTER                                                             
2  1   TABLE ACCESS (FULL) OF 'BOOK' (Cost=1 Card=1 Bytes=59)           
3  1   FILTER                                                           
4  3     NESTED LOOPS (Cost=6 Card=1 Bytes=82)                          
5  4       TABLE ACCESS (FULL) OF 'SALES' (Cost=1 Card=5 Bytes=90)         
6  4       TABLE ACCESS (BY INDEX ROWID) OF 'BOOK' (Cost=1 Card=1)
7  6         INDEX (UNIQUE SCAN) OF 'PK_BOOK' (UNIQUE)