In SQL, we can use the DROP TABLE IF EXISTS statement to drop a table only if it exists.

While it may seem obvious that we can only drop a table if it exists (i.e. we can’t drop a table that doesn’t exist), there’s a good reason for using this statement.

The reason we put an IF EXISTS clause into a DROP TABLE statement is to prevent any errors that would occur if the table doesn’t exist.

Example

Here’s an example of the SQL DROP TABLE IF EXISTS statement:

DROP TABLE IF EXISTS t1;

That statement drops a table called t1.

We can run that statement multiple times without getting an error. Even if the table exists the first time (and is therefore successfully dropped), we can run it again (after it has been dropped) without getting an error. That’s because the IF EXISTS prevents any such error from occurring.

Here’s the output when I run that statement three times in a row in MySQL:

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

In this case, there really was a table called t1 in the database. Therefore, the first line dropped the table.

This meant that by the time the second two lines ran, the table had already been dropped and it therefore no longer existed in order to be dropped again. In this case we didn’t get an error.

However, MySQL did present us with a warning, and we can check that warning as follows:

SHOW WARNINGS;

Result:

+-------+------+-------------------------+
| Level | Code | Message                 |
+-------+------+-------------------------+
| Note  | 1051 | Unknown table 'test.t1' |
+-------+------+-------------------------+

So the warning simply told us what we already knew – that it’s an “unknown” table (i.e. it doesn’t exist).

Here’s the output I get when I try to DROP TABLE IF EXISTS twice in PostgreSQL:

barney=# DROP TABLE IF EXISTS t1;
DROP TABLE
barney=# DROP TABLE IF EXISTS t1;
NOTICE:  table "t1" does not exist, skipping
DROP TABLE

The table previously existed, and so the first time I ran it I got a simple message DROP TABLE. However, the second time I ran it, I got a “notice” telling me that the table doesn’t exist, before it skipped it and ended with DROP TABLE.

You may or may not see such a warning, depending on your setup.

DROP TABLE Without IF EXISTS

Let’s try running DROP TABLE in MySQL without the IF EXISTS part:

DROP TABLE t1;

Result:

ERROR 1051 (42S02): Unknown table 'test.t1'

This time we get an error. In MySQL’s case, we get the same message that was returned by the warning. But in other RDBMSs we could get slightly different messages.

Here’s what I get when I run that code in PostgreSQL:

ERROR:  table "t1" does not exist

And here’s what I get when I run that code in SQL Server:

Error: Cannot drop the table 't1', because it does not exist or you do not have permission.

So any “warnings” or “notices” turn into “errors”.

The problem with errors is that they completely halt the process. Warnings will inform you what happened without terminating the process. That’s why DROP TABLE IF EXISTS is such a handy statement to have in SQL.

Oracle Database

The DROP TABLE IF EXISTS syntax was introduced in Oracle Database 23c. Prior to this version, we had to do some extra work to check for the existence of a table before dropping it.

Here are two DROP TABLE IF EXISTS alternatives that we can use with Oracle pre-23c.