EXISTS (SQL)
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.