Many RDBMSs implement IF EXISTS and IF NOT EXISTS syntax modifiers that can be used with DDL object creation, modification, and deletion, such as CREATE TABLE and DROP TABLE statements, to name just a couple.

These syntax modifiers allow us to run such statements without getting an error in the event that the object already exists (if we’re trying to create it or modify it) or doesn’t exist (if we’re trying to drop it).

Until recently, the IF EXISTS and IF NOT EXISTS syntax modifiers were unavailable in Oracle Database. In Oracle, we’d need to do a bit of extra work if we wanted to avoid such errors during DDL object creation, modification, and deletion.

But Oracle Database 23c changes this. The IF EXISTS and IF NOT EXISTS syntax modifiers were introduced in Oracle Database 23c.

The IF EXISTS Modifier

We can use the IF EXISTS modifier when we try to drop an object.

Example:

DROP TABLE IF EXISTS t1;

That statement drops a table called t1 if it exists. The IF EXISTS modifier ensures that we don’t get an error if the table doesn’t exist.

The IF NOT EXISTS Modifier

We can use the IF NOT EXISTS modifier when we try to create an object.

Example:

CREATE TABLE IF NOT EXISTS t1 (
  c1 number(6,0),
  c2 varchar2(10)
);

That statement creates a table called t1 if it doesn’t already exist. The IF NOT EXISTS modifier ensures that we don’t get an error if the table already exists.

As mentioned, prior to Oracle Database 23c, we had to do a bit of extra work if we wanted to check for the existence/non-existence of the object before performing such actions.

For example, here are some DROP TABLE IF EXISTS Alternatives for Oracle and here are 3 Ways to Create a Table if it Doesn’t Already Exist in Oracle (which includes the above example).