The EXISTS keyword

The keyword EXISTS is known as an existential qualifier because the subquery is true only if the outer SELECT, as the following query shows, finds at least one row.

Figure 1. Query

SELECT UNIQUE manu_name, lead_time 
   FROM manufact
   WHERE EXISTS
      (SELECT * FROM stock 
         WHERE description MATCHES '*shoe*'
            AND manufact.manu_code = stock.manu_code);

You can often construct a query with EXISTS that is equivalent to one that uses IN. The following query uses an IN predicate to construct a query that returns the same result as the query above.

Figure 2. Query

SELECT UNIQUE manu_name, lead_time 
   FROM stock, manufact
   WHERE manufact.manu_code IN
      (SELECT manu_code FROM stock 
         WHERE description MATCHES '*shoe*')
            AND stock.manu_code = manufact.manu_code;

Figure 1 and Figure 2 return rows for the manufacturers that produce a kind of shoe, as well as the lead time for ordering the product. The result shows the return values.

Figure 3. Query result

manu_name       lead_time 

Anza               5
Hero               4
Karsten           21
Nikolus            8
ProCycle           9
Shimara           30

Add the keyword NOT to IN or to EXISTS to create a search condition that is the opposite of the condition in the preceding queries. You can also substitute !=ALL for NOT IN.

The following query shows two ways to do the same thing. One way might allow the database server to do less work than the other, depending on the design of the database and the size of the tables. To find out which query might be better, use the SET EXPLAIN command to get a listing of the query plan. SET EXPLAIN is discussed in your and HCL Informix® Guide to SQL: Syntax.

Figure 4. Query

SELECT customer_num, company FROM customer
  WHERE customer_num NOT IN
    (SELECT customer_num FROM orders
       WHERE customer.customer_num = orders.customer_num);

SELECT customer_num, company FROM customer
  WHERE NOT EXISTS
    (SELECT * FROM orders
       WHERE customer.customer_num = orders.customer_num);

Each statement in the query above returns the following rows, which identify customers who have not placed orders.

Figure 5. Query result

customer_num company

         102 Sports Spot
         103 Phil's Sports
         105 Los Altos Sports
         107 Athletic Supplies
         108 Quinn's Sports
         109 Sport Stuff
         113 Sportstown
         114 Sporting Place
         118 Blue Ribbon Sports
         125 Total Fitness Sports
         128 Phoenix University

The keywords EXISTS and IN are used for the set operation known as intersection, and the keywords NOT EXISTS and NOT IN are used for the set operation known as difference. These concepts are discussed in Set operations.

The following query performs a subquery on the items table to identify all the items in the stock table that have not yet been ordered.

Figure 6. Query

SELECT * FROM stock 
   WHERE NOT EXISTS
      (SELECT * FROM items 
         WHERE stock.stock_num = items.stock_num
            AND stock.manu_code = items.manu_code);

The query returns the following rows.

Figure 7. Query result

stock_num manu_code description unit_price unit unit_descr

      101  PRC        bicycle tires       $88.00  box   4/box
      102  SHM        bicycle brakes     $220.00  case  4 sets/case
      102  PRC        bicycle brakes     $480.00  case  4 sets/case
      105  PRC        bicycle wheels      $53.00  pair  pair
      ⋮
      312  HRO        racer goggles       $72.00  box   12/box
      313  SHM        swim cap            $72.00  box   12/box
      313  ANZ        swim cap            $60.00  box   12/box

No logical limit exists to the number of subqueries a SELECT statement can have.

Perhaps you want to check whether information has been entered correctly in the database. One way to find errors in a database is to write a query that returns output only when errors exist. A subquery of this type serves as a kind of audit query, as the following query shows.

Figure 8. Query

SELECT * FROM items
   WHERE total_price != quantity *
      (SELECT unit_price FROM stock
         WHERE stock.stock_num = items.stock_num
            AND stock.manu_code = items.manu_code);

The query returns only those rows for which the total price of an item on an order is not equal to the stock unit price times the order quantity. If no discount has been applied, such rows were probably entered incorrectly in the database. The query returns rows only when errors occur. If information is correctly inserted into the database, no rows are returned.

Figure 9. Query result

item_num order_num stock_num manu_code quantity total_price 

       1      1004         1 HRO              1     $960.00
       2      1006         5 NRG              5     $190.00