What is difference between delete vs drop vs truncate

DELETE, DROP and TRUNCATE all three are the Commands of SQL. these three commands look similar but they are different from each other. every time student faces an interview on SQL they asked been this question. so it is more important as students to know the complete difference between them.

What is the DELETE command in SQL?

The SQL DELETE command is a DML (data manipulation language) command that is used to delete existing records from the table in the database. This delete command will remove records depending on the condition given with the where Clause.

Syntax – the SQL DELETE command

DELETE FROM table_name WHERE condition;

What is the TRUNCATE command in SQL?

The SQL TRUNCATE command is a DDL (data definition language) command that is used to delete all the Rows or Records from the table in the database. But it doesn’t remove the structure of the table.

this TRUNCATE command does not use the where clause like the DELETE command. It is faster than the DELETE command because it does not have to check conditions.

Syntax: the SQL TRUNCATE command

TRUNCATE table table_name;

What is a DROP command in SQL?

The SQL DROP command is DDL (data definition language) command that is used to delete a complete table with data from the database.

Syntax: the SQL DROP command

DROP table table_name;

What is the difference between DELETE, DROP and TRUNCATE

DELETE

• The DELETE command is the DML (Data Manipulation Language) Command.
• The DELETE Command Deletes one or more existing records from a table in the database. 
• We can restore any deleted rows from the database using the ROLLBACK commands.
• The DELETE command works slower than the DROP command and TRUNCATE command as well.

TRUNCATE

• The TRUNCATE command is the DDL (Data Defination Language) Command.
• The TRUNCATE command deletes all Rows from the table but does not delete the column name.
• We can’t restore all the deleted records from the database using the ROLLBACK command.
• The TRUNCATE command works faster than the DROP command and DELETE commands.

DROP

• The DROP command is the DDL (Data Definition Language) command.
• The DROP command drops the complete table from the database.
• We can’t restore the table from the database using the ROLLBACK command.
• The DROP command works faster than the DELETE Command but Slower than the TRUNCATE command.