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:
DELETE FROM Table_Name;
This query deletes all records from the given table name.
DELETE Command Syntax with WHERE Clause:
DELETE FROM Table_Name WHERE [Conditions];
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)
DELETE FROM Student WHERE Student_Id>100;
This query deletes all rows from table Student as WHERE clause and the conditions are not specified.
DELETE FROM Student;
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:
DROP TABLE Table_Name;
Example Queries:
This query will drop the table Student from the database
DROP TABLE Student;
This query drops/deletes the entire Database School.
DROP 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:
TRUNCATE TABLE Table_Name;
Example Queries:
This query removes all records from table Student.
TRUNCATE 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.