Mimer SQL Reference Manual TOC PREV NEXT INDEX

Mimer Developer Site

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
Mimer SQL Reference Manual TOC PREV NEXT INDEX