Manav Paul
SQL 101 : Difference between DELETE, DROP and TRUNCATE

SQL 101 : Difference between DELETE, DROP and TRUNCATE

SQL Data languages commands that looks similar but perform different operations.

Manav Paul's photo
Manav Paul
·

4 min read

Table of contents

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?

DELETEDROPTRUNCATE
LanguageThe DELETE command is Data Manipulation Language Command.The DROP command is Data Definition Language Command.The TRUNCATE command is a Data Definition Language Command.
UseThe 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.
TransitionWe 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 SpaceThe 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 SpeedThe 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 ConstraintsThe Integrity Constraints remain the same in the DELETE commandThe Integrity Constraints get removed for the DROP command.The Integrity Constraints will not get removed from the TRUNCATE command.
PermissionDELETE 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.
SyntaxDELETE 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! ♥

SQL#SQLtutorial DBMSRDBMSMySQL