EXISTS (SQL)

Synopsis

EXISTS select-statement

DROP-command IF EXISTS name

Description

The EXISTS predicate is used to test 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...)

where a typical statement might be:

SELECT name
     FROM Table_A
     WHERE EXISTS
     (SELECT *
          FROM Table_B
          WHERE Table_B.Number = Table_A.Number)

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))

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

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.

The variation IF EXISTS can be used to condition the execution of a DROP command (such as DROP TABLE) on the existence of its target, as in the following statement:

DROP TABLE IF EXISTS Records

In this example, no error will occur if the table Records does not exist. The statement will return SQLCODE 1 and a message. This behavior takes priority to settings which govern DDL statements in the Management Portal or the configuration parameter file (CPF), which suppress the error silently.

Similarly, IF NOT EXISTS can be specified when using the command CREATE TABLE, as in the following statement:

CREATE TABLE IF NOT EXISTS Records (...)

In this example, if a Records table already exists, the command will do nothing. No error will occur, and the statement will return SQLCODE 1 and a message. This behavior takes priority over settings which govern DDL statements in the Management Portal or the configuration parameter file (CPF), which effectively overwrite the existing table and suppress the error silently. For further details, consult the section on methods to check for existing tables on the CREATE TABLE reference page.

Arguments

See Also

• SELECT statement, HAVING clause, WHERE clause

• Overview of Predicates