Skip to main content

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

Ignoring a CREATE TABLE command if target already exists, suppressing errors

CREATE TABLE IF NOT 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. 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.

See Also

FeedbackOpens in a new tab