DROP TABLE or DELETE TABLE command in SQL

Deleting tables in SQL is used quite a lot. This term is also known as drop table in SQL. Here's everything you need to know about deleting tables in SQL.

Deleting tables in SQL is used quite a lot. This term is also known as drop table in SQL. Here's everything you need to know about deleting tables in SQL .

SQL is knowledge that every programmer needs to master. In fact, learning SQL is not difficult. Let's start getting acquainted with the simplest commands. In this article is the command to delete a table in SQL.

Drop table in SQL is a data definition language command (Data Definition Language - DDL). It is used to remove a table's definition, data, indexes, triggers, constraints, and associated permission characteristics if any.

Note:

You should be careful when using this command because when you delete a table, all information available within it will be lost forever.
To delete a SQL table in the database, you need ALTER permission on the table in question and CONTROL permission on the table diagram.
Although it is a data definition language command, it differs from the TRUNCATE TABLE command because the DROP command completely deletes the table from memory.

Basic syntax of delete table command in SQL:

DROP TABLE [IF EXISTS] [database_name.][schema_name.]table_name;

In this syntax:

First, select the name of the table to be deleted.
Second, select the name of the database in the created table and the name of the schema to which the table belongs. The database name is optional. If omitted, the drop table command will delete the table in the currently connected database.
Third, use If Exists to only drop the table if it exists.

Example of DROP TABLE command

We must first confirm that the NHANVIEN table exists before deleting the table from the database, as in the example below:

SQL> DESC NHANVIEN; +-----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------------+------+-----+---------+-------+ | ID | int(11) | NO | PRI | | | | TEN | varchar(20) | NO | | | | | TUOI | int(11) | NO | | | | | DIACHI | char(25) | YES | | NULL | | | LUONG | decimal(18,2) | YES | | NULL | | +-----------+---------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)

This means that the NHANVIEN table exists in the database, so we will now delete it with the DROP TABLE command:

SQL> DROP TABLE NHANVIEN; Query OK, 0 rows affected (0.01 sec)

Now you can check to see if the table deletion was successful by using the DESC command again:

SQL> DESC NHANVIEN; ERROR 1146 (42S02): Table 'QTM.NHANVIEN' doesn't exist

If you receive the above error message, it means the table has been deleted from the database.

In QTM.NHANVIEN, NHANVIEN is the table name, and QTM is the illustrative database used for the examples in this series.

Compare DELETE vs. TRUNCATE vs. DROP

DELETE	TRUNCATE	DROP
This is a DML command	This is a DDL command	This is a DDL command
Conditions DELETE FROM tble_nameWHERE	TRUNCATE TABLE table_name;	DROP TABLE table_name;
It removes one or more records from the table	It removes all rows in the database	Completely remove the table from the database
Do not release allocated table space	It completely removes the space allocated to the table on memory	Completely remove allocated space from memory
It runs slower than the DROP and TRUNCATE commands because it deletes rows one at a time based on specific conditions.	It runs faster than both DELETE and TRUNCATE because it deletes the entire record at once without any conditions.	Runs faster than DELETE but slower than TRUNCATE. It deletes the row first, then the table in the database.