Skip to main content
Previous section   Next section

EXISTS

Checks a table for the existence of at least one corresponding row.

Synopsis

EXISTS select-statement

Arguments

Argument Description
select-statement A simple query, usually containing a condition expression.

Description

The EXISTS predicate tests a specified table, typically for existence of at least a row. Since the SELECT statement following the EXISTS is being checked for containing something, the clause is often of the form:

EXISTS (SELECT... FROM... WHERE...)
Copy code to clipboard

where a typical statement might be:

SELECT name
     FROM Table_A
     WHERE EXISTS
     (SELECT *
          FROM Table_B
          WHERE Table_B.Number = Table_A.Number)
Copy code to clipboard

In this example, the predicate tests for the existence of one or more rows specified by the subquery.

Note that the test must occur on a SELECT statement (not on a UNION).

The NOT EXISTS clause tests for the non-existence of a row in a table, as shown in the following example:

SELECT EmployeeName,Age
     FROM Employees
     WHERE NOT EXISTS (SELECT * FROM BonusTable
     WHERE NOT (BonusTable.Result = 'Positive'
     AND Employees.EmployeeNum = BonusTable.EmployeeNum))
Copy code to clipboard

EXISTS can be used wherever a predicate condition can be specified, as described in the Overview of Predicates page of this manual.

Where applicable, the system automatically applies Set-Valued Subquery Optimization (SVSO) to an EXISTS or NOT EXISTS subquery. For details on this optimization, and using the %NOSVSO keyword to override it, refer to “Query Optimization Options” on the FROM clause reference page.

See Also

Previous section   Next section