This article will be looking into how to drop a table if it exists in the database.

How to DROP Table IF EXISTS in MySQL

What all does a DROP TABLE statement does?

  • DROP TABLE statement will remove one or more table ( we must have the DROP privilege for each table).
  • Will remove the table definition and all table data. 
  • Will drop any triggers for the table.
  • If the table is partitioned, DROP TABLE will remove the partitions and all the data present in those partitions.

Syntax:- DROP TABLE [IF EXISTS] table_name [, table_name] …

IF EXISTS clause in the DROP statement is optional and has a role to play in its behaviour.

  • IF EXISTS clause present: When we run a DROP statement and if the given table does not exist, there will be no error occurring, but a warning message will be displayed.
  • IF EXISTS clause absent: When we run a DROP statement, and if the given table does not exist, the DROP statement will fail, displaying that an error has occurred for the non-existing tables which could not be deleted.

Let us look into an example. There is an existing table sale_details

Run the below query to verify the existence of the table.

SHOW TABLES LIKE "sale_details";

Output:-

figure 1.1

The result shows that table sale_details exist in the database.We will now be dropping this table, including IF EXISTS in the DROP statement. Observe the below statement and its output.

DROP TABLE IF EXISTS sale_details;

Action Output Message: DROP TABLE IF EXISTS sale_details 0 row(s) affected 0.023 sec

Again verify the table exists or not by running query “SHOW TABLES LIKE “sale_details”;” and observe the output.

figure 1.2

As we can see in the output, no tables are present in the result. Hence the table was dropped successfully. Since our table sale_details table does not exist, we will again run the DROP TABLE statement, including the IF EXISTS clause. Observe the below query.

DROP TABLE IF EXISTS sale_details;

Action Output Message: DROP TABLE IF EXISTS sale_details 0 row(s) affected, 1 warning(s): 1051 Unknown table ‘riti_sales_db.sale_details’ 0.0017 sec

figure 1.3

It shows a warning and not an error. Let us remove the IF EXISTS clause and watch the output.

DROP TABLE sale_details;

Action Output Message: DROP TABLE sale_details Error Code: 1051. Unknown table ‘riti_sales_db.sale_details’ 0.0013 sec

figure 1.4

This time it shows an error and not a warning.

How to DROP Temporary Table IF EXISTS in MySQL

A temporary table is a table that will store a temporary result set, which can be retrieved many times in a single session. DROP statement works the same way for temporary tables as well.

Syntax:- DROP TEMPORARY TABLE [IF EXISTS] table_name

We will create a temporary table sales_person_department from the sale_details table.

CREATE TEMPORARY TABLE sales_person_department
SELECT sale_person_name,sales_department FROM sale_details;

Action Output Message: CREATE TEMPORARY TABLE sales_person_department SELECT sale_person_name,sales_department FROM sale_details 12 row(s) affected Records: 12 Duplicates: 0 Warnings: 0 0.0023 sec.

The temporary table sales_person_department got created successfully. Now try dropping it using the IF EXISTS clause in DROP statement.

DROP TEMPORARY TABLE IF EXISTS sales_person_department;

Action Output Message:- DROP TEMPORARY TABLE IF EXISTS sales_person_department 0 row(s) affected 0.00046 sec.

figure 1.5

Table sales_person_department has been dropped successfully.

If we re-run the query to drop the temporary table sales_person_department, we will get a warning message.

DROP TEMPORARY TABLE IF EXISTS sales_person_department;

Action Output Message: DROP TEMPORARY TABLE IF EXISTS sales_person_department 0 row(s) affected, 1 warning(s): 1051 Unknown table ‘riti_sales_db.sales_person_department’ 0.00035 sec

figure 1.6
We hope this article provides a good understanding of DROP TABLE IF EXISTS in MySQL. Good Luck !!!