EXISTS Subquery

EXISTS Subquery:
 
|--+-----+--EXISTS--(--subquery--)------------------------------|
   '-NOT-'
 

An EXISTS subquery condition evaluates to TRUE if the subquery returns a row. With an EXISTS subquery, one or more columns can be returned. The subquery always contains a reference to a column of the table in the main query. If you use an aggregate function in an EXISTS subquery that includes no HAVING clause, at least one row is always returned.

The following example of a SELECT statement with an 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). You can appropriately use an EXISTS subquery in this SELECT statement because you use the subquery to test both stock_num and manu_code in items.

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 works equally well if you use SELECT * in the subquery in place of the column names, because the existence of the entire row is tested; specific column values are not tested.