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 .

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.

DROP TABLE command

Samuel Daniel

Update 17 October 2023

You should read it

May be interested

CREATE TABLE command in SQL to create a database table
what does the create table command in sql do? in this article, let's learn everything you need to know about the create table statement in sql!
Expressions in SQL
an expression is a combination of one or more values, operators, and sql functions that evaluate to a value. these sql expressions are like formulas and they are written in query language.
Basic SQL syntax
sql has a set of rules and instructions called syntax (syntax). this article will quickly list for you the basic sql syntax.
Relational database management system RDBMS in SQL
what is rdbms? rdbms stands for relational database management system. rdbms is the foundation for sql as well as for all other database systems such as ms sql server, ibm db2, oracle, mysql and microsoft access.
Overview of SQL
sql is a language for working with databases that includes many operations such as creating databases, deleting, extracting data, modifying data, etc. sql has many versions.
Instructions on how to install MySQL on Windows 11 computers
detailed instructions step by step to install mysql on windows 11. how to set up mysql server on the most complete windows 11 computer