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. The following figure illustrates the intersection set operation.

Figure 1. The intersection set operation

This figure is described in the surrounding text.

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

Figure 2. Query

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

Figure 3. Query result

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