Overview

The EXISTS predicate is very natural. It is a test for a nonempty set. If there are any rows in its subquery, it is TRUE; otherwise, it is FALSE. This predicate does not give an UNKNOWN result. The syntax is:

 ::= EXISTS 

It is worth mentioning that a

In SQL-89, the rules stated that the subquery had to have a SELECT clause with one column or a *. If the SELECT * option was used, the database engine would (in theory) pick one column and use it. This fiction was needed because SQL-89 defined subqueries as having only one column.

Some early SQL implementations would work better with EXISTS(SELECT ...), EXISTS(SELECT ...), or EXISTS(SELECT * ...) versions of the predicate. Today, there is no difference in the three forms in the major products, so the EXISTS(SELECT * ...) is the preferred form.

Indexes are very useful for EXISTS() predicates because they can be searched while the base table is left alone completely. For example, we want to find all employees who were born on the same day as any famous person. The query could be:

SELECT P1.emp_name, ' has the same birthday as a famous person!'   FROM Personnel AS P1 WHERE EXISTS       (SELECT *...