EXISTS Subquery

From the EXISTS subquery, rows that satisfy EXISTS conditions in one or more columns can be returned. (Similarly, the NOT EXISTS subquery can returns rows that satisfy NOT EXISTS conditions in one or more columns.)

The following example of a SELECT statement with a NOT EXISTS subquery returns the stock number and manufacturer code for every item that has never been ordered (and is therefore not listed in the items table).

It is appropriate to use a NOT EXISTS subquery in this SELECT statement because you need the correlated subquery to test both stock_num and manu_code in the items table. 
SELECT stock_num, manu_code FROM stock
   WHERE NOT EXISTS
      (SELECT stock_num, manu_code FROM items
         WHERE stock.stock_num = items.stock_num AND
            stock.manu_code = items.manu_code);

The preceding example would work equally well if you use a SELECT * in the subquery in place of the column names, because you are testing for the existence of a row or rows.

For additional information, see the EXISTS Subquery condition.