Checking a table for the existence of at least one row
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
|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.|
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.