Intersection

The intersection of two sets of rows produces a table that contains rows that exist in both the original tables. Use the keyword EXISTS or IN to introduce subqueries that show the intersection of two sets. Figure 344 illustrates the intersection set operation.

Figure 344. The Intersection Set Operation

Figure 345 is an example of a nested SELECT statement that shows the intersection of the stock and items tables. Figure 346 contains all the elements that appear in both sets and returns the following rows.

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

stock_num manu_code unit_price

        1 HRO          $250.00
        1 HSK          $800.00
        1 SMT          $450.00
        2 HRO          $126.00
        3 HSK          $240.00
        3 SHM          $280.00
       
·
·
·

      306 SHM          $190.00
      307 PRC          $250.00
      309 HRO           $40.00
      309 SHM           $40.00