EXISTS predicate

The EXISTS predicate tests for the existence of certain rows. The fullselect can specify any number of columns, and can result in true or false.

Notes:

¹ The outer SELECT list of fullselect must not contain an array value.

The result of the EXISTS predicate:

Is true only if the number of rows that is specified by the fullselect is not zero.
Is false only if the number of rows specified by the fullselect is zero.
Cannot be unknown.

The SELECT clause in the fullselect can specify any number of columns because the values returned by the fullselect are ignored. For convenience, use:

  SELECT *

Unlike the NULL, LIKE, and IN predicates, the EXISTS predicate has no form that contains the word NOT. To negate an EXISTS predicate, precede it with the logical operator NOT, as follows:

  NOT EXISTS (fullselect)

The result is then false if the EXISTS predicate is true, and true if the predicate is false. Here, NOT is a logical operator and not a part of the predicate. Logical operators are discussed in Search conditions.

Example 1: The following query lists the employee number of everyone represented in DSN8C10.EMP who works in a department where at least one employee has a salary less than 20000. Like many EXISTS predicates, the one in this query involves a correlated variable.

  SELECT EMPNO
    FROM DSN8C10.EMP X
    WHERE EXISTS (SELECT * FROM DSN8C10.EMP
                   WHERE X.WORKDEPT=WORKDEPT AND SALARY<20000);

Example 2: List the subscribers (SNO) in the state of California who made at least one call during the first quarter of 2009. Order the results according to SNO. Each MONTHnn table has columns for SNO, CHARGES, and DATE. The CUST table has columns for SNO and STATE.

  SELECT C.SNO
    FROM CUST C
    WHERE C.STATE = 'CA'
    AND EXISTS (
    SELECT * 
      FROM MONTH1
      WHERE DATE BETWEEN '01/01/2009 AND '01/31/2009'
      AND C.SNO = SNO
    UNION ALL
    SELECT * 
      FROM MONTH2
      WHERE DATE BETWEEN '02/01/2009 AND '02/28/2009'
      AND C.SNO = SNO
    UNION ALL
    SELECT * 
    FROM MONTH3
    WHERE DATE BETWEEN '03/01/2009 AND '03/31/2009'
    AND C.SNO = SNO
    )
    ORDER BY C.SNO;