Difference between DELETE, DROP and TRUNCATE in SQL

In this article, we look into the difference between DELETE, DROP and TRUNCATE commands in SQL. We will also see the general syntax related to each of the commands and the corresponding queries to implement these commands in SQL.

DELETE

The SQL DELETE command is used to delete existing records or rows from a table. The query can be used to delete one or multiple records from an existing table depending on the condition specified. The DELETE command is a Data Manipulation Language (DML) command. This command returns the number of records deleted after execution. With this command, we can also delete all the records of the table if we do not specify the condition in the WHERE clause.

DELETE Command Syntax to REMOVE all Rows:

This query deletes all records from the given table name.

DELETE Command Syntax with WHERE Clause:

Note: We do not need to list fields or column names of the table because the entire row or tuple is deleted.

Example Queries:

This query removes all records or rows where Student_Id>100 (Student_Id is a column or attribute of table Student)

This query deletes all rows from table Student as WHERE clause and the conditions are not specified.

DROP

The SQL DROP command is used to drop the whole table. In other words, it deletes the entire records as well as the schema and structure of the table along with its named elements. The DROP command is a Data Definition Language (DDL) command. In addition, this command can also be used to drop databases. Databases or tables once dropped are wiped out of existence and cannot be recovered.

DROP Command Syntax:

Example Queries:

This query will drop the table Student from the database

This query drops/deletes the entire Database School.

TRUNCATE

The SQL TRUNCATE command is used to delete all the records/rows from the table without affecting the schema and structure of the database. The TRUNCATE Command is a Data Definition Language (DDL) command. This command cannot be used to delete a single row in a database because the TRUNCATE command cannot use the WHERE clause.

TRUNCATE Command Syntax:

Example Queries:

This query removes all records from table Student.

Difference between DELETE, DROP and TRUNCATE

Now, we compare and contrast between key features of DELETE, DROP and TRUNCATE commands respectively.

DELETE	DROP	TRUNCATE
1. It is a DML command.	1. It is a DDL command.	1. It is a DDL command.
2. It is used to delete rows or records based on conditions specified in the WHERE clause.	2. It is used to delete the entire table along with its schema and structure respectively.	2. It is used to delete the entire records of a table without affecting the schema of the table.
3. If the WHERE clause is not specified with conditions it deletes all the records of the table.	3. There is no WHERE clause.	3. There is no WHERE clause.
4. It is a DML command. As a result, the operation can be rolled back.	4. It is a DDL command. As a result, the changes cannot be rolled back or undone.	4. It is a DDL command. As a result, the changes cannot be rolled back or undone.
5. It scans every row before deleting making it slower and time-consuming.	5. It is faster and time-saving.	5. It is faster than DELETE in execution because it does not scan every row before deleting which makes it the least time-consuming.
6. Syntax: DELETE FROM TABLE Table_Name WHERE [CONDITIONS];	6. Syntax: DROP TABLE Table_Name;	6. Syntax: TRUNCATE TABLE            Table_Name;

That’s it for the article you can create a sample table with some dummy records and execute each of the commands to see their working. You can ask your queries in the comment section below.