SQL 101 : Difference between DELETE, DROP and TRUNCATE
SQL Data languages commands that looks similar but perform different operations.
DROP Command in SQL?
The DROP command drops the existing table from the database. It only requires the table name to be dropped from the database.
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.
Syntax
DROP TABLE table_name;
Example
DELETE Command in SQL?
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
DELETE FROM TableName WHERE condition;
Example
TRUNCATE Command in SQL?
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 condition checking.
Syntax
TRUNCATE TABLE table_name;
Example
Difference between DELETE, DROP and TRUNCATE Commands in SQL?
| DELETE | DROP | TRUNCATE | |
| 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; |
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.
Thanks for taking the time to read this article!
▶Next : Master Solidity Series
Documenting my journey with Solidity, Blockchain and Web3
Drop a comment here, share or hit me up on Twitter! ♥