www.mimer.com/developer

The EXISTS Predicate

---

The EXISTS predicate tests whether the set of values addressed by a select-specification is empty or not, and has the form:

The result of the predicate is true if the select-specification does not result in an empty set. Otherwise the result of the predicate is false. A set containing only NULL values is not empty. The result is never unknown.

The EXISTS predicate is the only predicate which does not compare a value with one or more other values. The columns selected in the select-specification of an EXISTS predicate are irrelevant. Most commonly, the SELECT * shorthand is used.

The EXISTS predicate may be negated in the construction of search conditions. Observe however that NOT EXISTS predicates must be handled with care, particularly if empty result sets arise in the selection condition.

Consider the four following examples, and note particularly that the last example is true if all guests have undefined names:

 EXISTS (SELECT * FROM BOOK_GUEST
 WHERE GUEST = 'DATE')
 

   demands that at least one guest is called DATE

 NOT EXISTS (SELECT * FROM BOOK_GUEST
 WHERE GUEST = 'DATE')
 

   demands that no guest may be called DATE

 EXISTS (SELECT * FROM BOOK_GUEST
 WHERE NOT GUEST = 'DATE')
 

   demands that at least one guest is not called DATE

 NOT EXISTS (SELECT * FROM BOOK_GUEST
 WHERE NOT GUEST = 'DATE')
 

   demands that no guest may not be called DATE, i.e. every guest must be called DATE (or be NULL).

Upright Database Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 dbtechnology@upright.se