EXISTS Predicate

Tests the existence of at least one row that satisfies the selection criteria in a subquery.

Syntax

Notes: The result cannot be undefined. If the subquery returns at least one row, the result is True; otherwise, the result is False. The subquery need not return values for this predicate; therefore, you can list any columns in the select list that exist in the underlying tables or queries or use an asterisk (*) to denote all columns.

Example

To find all contacts that own at least one product, enter the following (qxmplContactsSomeProduct):

SELECT tblContacts.FirstName, tblContacts.MiddleInit, tblContacts.LastName
 FROM tblContacts
 WHERE EXISTS
  (SELECT *
    FROM tblContactProducts
     INNER JOIN tblProducts
     ON tblContactProducts.ProductID = tblProducts.ProductID
    WHERE tblContactProducts.ContactID = tblContacts.ContactID
     AND tblProducts.TrialVersion = 0);

Note In this example, the inner subquery makes a reference to the tblContacts table in the SELECT statement by referring to a column in the outer table (tblContacts.ContactID). This forces the subquery to be evaluated for every row in the SELECT statement, which might not be the most efficient way to achieve the desired result. (This type of subquery is also called a correlated subquery.) Whenever possible, the database query plan optimizer solves the query efficiently by reconstructing the query internally as a join between the source specified in the FROM clause and the subquery. In many cases, you can perform this reconstruction yourself, but the purpose of the query might not be as clear as when you state the problem using a subquery.

See also Expression, SELECT Statement, Subquery, and WHERE Clause in this tutorial.