Difference

The difference between two sets of rows produces a table that contains rows in the first set that are not also in the second set. Use the keywords NOT EXISTS or NOT IN to introduce subqueries that show the difference between two sets. Figure 347 illustrates the difference set operation.

Figure 347. The Difference Set Operation

Figure 348 is an example of a nested SELECT statement that shows the difference between the stock and items tables.

SELECT stock_num, manu_code, unit_price FROM stock
   WHERE stock_num NOT IN
      (SELECT stock_num FROM items)
   ORDER BY stock_num

Figure 349 contains all the elements from only the first set, which returns 17 rows.

stock_num manu_code unit_price 

      102 PRC          $480.00
      102 SHM          $220.00
      106 PRC           $23.00
      
·
·
·

      312 HRO           $72.00
      312 SHM           $96.00
      313 ANZ           $60.00
      313 SHM           $72.00