Difference Between DROP, DELETE and TRUNCATE?

DELETE

The SQL DELETE command is a DML (Data Manipulation Language) command that deletes existing records from the table in the database. It can delete one or more rows from the table depending on the condition given with the WHERE clause. Thus the deletion of data is controlled according to the user's needs and requirements. The DELETE statement does not delete the table from the database. It just deletes the records present inside it and maintains a transaction log of each deleted row.

Syntax

The SQL DELETE command works with the following syntax:

DROP

The SQL DROP command is a DDL (Data Definition Language) command that deletes the defined table with all its table data, associated indexes, constraints, triggers, and permission specifications. The DROP command drops the existing table from the database. It only requires the name of the table to be dropped.

Syntax

The SQL DROP command works with the following syntax:

TRUNCATE

The SQL TRUNCATE command is a DDL (Data Definition Language) command that modifies the data in the database. The TRUNCATE command helps us delete the complete records from an existing table in the database. It resets the table without removing it from the database. It does not use the WHERE clause like the DELETE command to apply the condition. It requires the table name to delete the records. It has faster performance due to the absence of conditions checking.

Syntax

The SQL TRUNCATE command works with the following syntax:

Difference Between DELETE, DROP and TRUNCATE Commands in SQL: DROP vs TRUNCATE vs DELETE

Let us look at the brief comparison of SQL's DROP, DELETE and TRUNCATE commands.

Parameters	DELETE Command	DROP Command	TRUNCATE Command
Language	The DELETE command is Data Manipulation Language Command.	The DROP command is Data Definition Language Command.	The TRUNCATE command is a Data Definition Language Command.
Use	The DELETE command deletes one or more existing records from the table in the database.	The DROP Command drops the complete table from the database.	The TRUNCATE Command deletes all the rows from the existing table, leaving the row with the column names.
Transition	We can restore any deleted row or multiple rows from the database using the ROLLBACK command.	We cannot get the complete table deleted from the database using the ROLLBACK command.	We cannot restore all the deleted rows from the database using the ROLLBACK command.
Memory Space	The DELETE command does not free the allocated space of the table from memory.	The DROP command removes the space allocated for the table from memory.	The TRUNCATE command does not free the space allocated for the table from memory.
Performance Speed	The DELETE command performs slower than the DROP command and TRUNCATE command as it deletes one or more rows based on a specific condition.	The DROP Command has faster performance than DELETE Command but not as compared to the Truncate Command because the DROP command deletes the table from the database after deleting the rows.	The TRUNCATE command works faster than the DROP command and DELETE command because it deletes all the records from the table without any condition.
Integrity Constraints	The Integrity Constraints remain the same in the DELETE command.	The Integrity Constraints get removed for the DROP command.	The Integrity Constraints will not get removed from the TRUNCATE command.
Permission	DELETE permission is required to delete the rows of the table.	We need ALTER permission on the schema to which the table belongs and CONTROL permission on the table to use the DROP command.	We need table ALTER permission to use the TRUNCATE command.
Syntax	DELETE FROM table_name WHERE condition;	DROP TABLE table_name;	TRUNCATE TABLE table_name;

Example of DELETE, DROP, and TRUNCATE commands in SQL

DELETE Example

Let's consider the table Student with the following data.

Name	Roll_No
Nisha	101
Suman	102
Amish	103
Durgesh	104
Sarfaraz	105
Khansa	106

Let us understand the working of the DELETE command with the help of an example.

The table Student contains multiple rows. The above SQL Query helps us filter the appropriate row that needs to be deleted by the user. The WHERE clause helps us find the row with the student whose roll number is 101. The DELETE command deletes the row returned by the WHERE clause. In the above SQL query, the DELETE command deletes the record of student Nisha from the table whose roll number is 101.

The resulting table after the execution of the above SQL Query is:

Name	Roll_No
Suman	102
Amish	103
Durgesh	104
Sarfaraz	105
Khansa	106

DROP Example

Let us consider a table Student in the database.

The above SQL Query removes all the records and drops the table Student by removing it permanently from the database. The table removed using the DROP command can be retrieved back after execution.

TRUNCATE Example

Let's consider the table Student to understand the working of the TRUNCATE command in a better way.

The execution of the above SQL Query deletes all the records from the STUDENT table, leaving only the column names. It only removes the records from the table but doesn't remove the table from the database.

The resulting table after the execution of the above SQL Query is:

Name	Roll_No

Conclusion

The SQL DROP command is a DDL ( Data Definition Language ) command that deletes the defined table with all its table data, associated indexes, constraints, triggers, and permission specifications.
The SQL DELETE command is a DML ( Data Manipulation Language ) command that deletes existing records from the table in the database.
The SQL TRUNCATE command is a DDL ( Data Definition Language ) command that modifies the data in the database. The TRUNCATE command helps us delete the complete records from an existing table in the database.