One of the problems with SQL is that the same query can be expressed in a variety of ways, all with identical results but widely varying execution plans and readability.  In the example below, the following three SQL queries all produce the same output, but they look quite different.  Each of the below queries produces a list of books that have no sales.  Here is the output:

 

BOOK_TITLE                                                                     

----------------------------------------                                       

was george washington gay?                                                     

cooking light                                                                  

never eat boogers                                                              

how to housebreak your horse                                                    

 

 

Your assignment is as follows:

 

1. Copy these query and run it against the pubs database to verify that the same results are returned.

 

2. Once the output is confirmed, investigate each for of the subquery with a Google search and a review of Chapter 19.

 

3. Write a 300 word paper outlining your conclusions about the most efficient way to get the desired output and your recommendations on the most efficient and readable way to code this SQL query.

 

column book_title format a40

 

-- FORM 1

 

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

 

-- FORM 2

 

select

   book_title

from

   book

where

   book_key not in (select book_key from sales);

 

 

 

-- FORM 3

 

select

   book_title,

   nvl(quantity,0)

from

   book t,

   sales  s

where

   t.book_key = s.book_key(+)

and

   quantity is null

;