MySQL IF EXISTS DROP Table

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
• How to DROP Temporary Table IF EXISTS in MySQL

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:-

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.

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

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

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.

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

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