Checks for the existence of a given object.
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:
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:
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 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.
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)Opens in a new tab, 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.