EXISTS (SQL)

Checks for the existence of a given object.

Synopsis

Checking a table for the existence of at least one row

EXISTS select-statement

Suppressing errors if the target of a DROP command does not exist

DROP-command IF EXISTS name

Arguments

Argument	Description
select-statement	A simple query, usually containing a condition expression.
DROP-command	One of the following commands: DROP AGGREGATE, DROP DATABASE, DROP FUNCTION, DROP INDEX, DROP METHOD, DROP PROCEDURE, DROP QUERY, DROP ROLE, DROP TABLE, DROP TRIGGER, DROP USER, DROP VIEW.

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 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. To suppress such errors silently, adjust the settings which govern DDL statements in the Management Portal or the configuration parameter file (CPF)Opens in a new tab.