SQL provides command to DROP an existing table completely in a database. Once SQL DROP command is issued then there is no way back to recover the table including its data, so be careful before issuing this command in production system.

The SQL DROP Table Statement

The SQL DROP TABLE statement is a Data Definition Language (DDL) command that is used to remove a table”s definition, and its data, indexes, triggers, constraints and permission specifications (if any).

Note −

• You should be very careful while using this command because once a table is deleted then all the information available in that table will also be lost forever.

• If the table is partitioned, the statement removes the table definition, all its partitions, all data stored in those partitions, and all partition definitions.

• To drop a table in a database, one must require ALTER permission on the said table and CONTROL permissions on the table schema.

• Even though it is a data definition language command, it is different from TRUNCATE TABLE statement as the DROP statement completely frees the table from the memory.

• DROP TABLE causes an implicit commit, except when used with the TEMPORARY keyword.

Syntax

The basic syntax of this DROP TABLE statement is as follows −

DROP TABLE table_name;

Example

Assume we have created a table named CUSTOMERS using the CREATE TABLE statement as shown below −

CREATE TABLE CUSTOMERS(
   ID          INT NOT NULL,
   NAME        VARCHAR (20) NOT NULL,
   AGE         INT NOT NULL,
   ADDRESS     CHAR (25),
   SALARY      DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Let us first verify the CUSTOMERS table using the DESC command then we will delete it from the database −

DESC Table

If the table is created successfully the DESC command displays the structure of the table as shown below −

This means that the CUSTOMERS table is available in the database, so let us now drop it as shown below.

DROP TABLE CUSTOMERS;

Output

The output is displayed as follows −

Query OK, 0 rows affected (0.001 sec)

Verification

Now, to verify if the table is actually dropped, you can use the DESC CUSTOMERS command as shown −

DESC CUSTOMERS;

Following error is displayed −

ERROR 1146 (42S02): Table ''tutorials.CUSTOMERS'' doesn''t exist

When a MySQL table is dropped using SQL DROP command, privileges granted specifically for the table are not automatically dropped. They must be dropped manually.

The IF EXISTS Clause

Instead of always checking if the table exists or not in a database before dropping it, you can use the IF EXISTS clause in the DROP TABLE statement.

This clause, when specified in the DROP TABLE query, will automatically check whether the table exists in the current database and then drops it, if yes. If the table does not exist in the database, the query will be ignored.

Syntax

Following is the basic syntax of DROP TABLE IF EXISTS −

DROP TABLE [IF EXISTS] table_name;

Example

If you try to drop a table that doesn”t exist in the database, without using the IF EXISTS clause, as shown below −

DROP TABLE CUSTOMERS;

An error will be generated −

ERROR 1051 (42S02): Unknown table ''tutorials.CUSTOMERS''

If you use the IF EXISTS clause along with the DROP TABLE statement as shown below, the specified table will be dropped and if a table with the given name, doesn”t exist the query will be ignored.

But if you try to drop a table that does not exist in a database, using the IF EXISTS clause, as shown below −

DROP TABLE IF EXISTS CUSTOMERS;

The query will be ignored with the following output displayed −

Query OK, 0 rows affected, 1 warning (0.001 sec)

DROP – TEMPORARY TABLE

You can include TEMPORARY keyword with DROP TABLE statement which will drop only TEMPORARY tables. Including the TEMPORARY keyword is a good way to prevent accidentally dropping non-TEMPORARY tables.

Syntax

DROP TEMPORARY TABLE TEMP_TABLE;

Example

Following is an example to delete a temporary table CUSTOMERS.

DROP TEMPORARY TABLE CUSTOMERS;