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

Trick to quickly delete a table in Word
to delete a table in word is not as simple as you think. if you use the usual delete key to delete, it cannot be deleted. in the article below, network administrator will show you how to delete a table in word.
Delete a Table in Word - Delete a Table in Word
delete a table in word - delete a table in word. if you have difficulty deleting the table and its contents, see the article below.
TEMPORARY TABLE temporary table in SQL
temporary table, also known as temporary table, is a special form of table that is temporarily stored and handles intermediate results on sql
DELETE command in SQL Server
the delete statement in sql server (transact-sql) is used to delete one or more records from a table in sql server.
How to delete table borders in Word
how to delete table borders in word, which is a question that many people will probably wonder if not formed in using word software. first you need to black out the table cell to remove borders. the highlighted method is to click and drag the mouse to the cells you need to select.
The command bootcfg delete in Windows
the bootcfg delete command deletes the operating system entry in the [operating systems] section of the boot.ini file. the command applies to: windows server (semi-annual channel), windows server 2016, windows server 2012 r2, windows server 2012.
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!
How to delete table format in Excel
how to quickly delete the format on the excel table, to bring the data table back to the normal data area?
How to draw a table - Add, edit, and delete column rows in a table in Excel 2016
the following article will guide you in detail how to draw a table - add, edit, delete column rows in excel 2016. select the data area to create the table -> on the insert tab -> tables -> table
How to delete table lines in Word
how to delete table lines in word. creating tables in word is quite simple, but when working with tables in word, you want to delete some table lines to suit your content. if you do not know how to delete table lines in word, then please c