Question

Explain the similarities and differences between Drop (a table), Truncate (the data), and Delete (the data).

Answer 1

Difference between DELETE, DROP and TRUNCATE

1. DELETE : Delete is Data Manipulation Language statement which is used used only for deleting data from a table, not to remove the table from the database.

Delete Statement does not change any property of database.

With the help of “DELETE” command we can either delete all the rows in one go or can delete row one by one. i.e., we can use it as per the requirement or the condition using Where clause.

It is comparatively slower than TRUNCATE cmd.

This operation uses all Delete triggers.

Syntax : If we want to delete all the rows of the table:

DELETE from table_name; 

Syntax: If we want to delete the row of the table as per the condition then we use WHERE clause,

DELETE from table_name WHERE condition; 
You can use the “ROLLBACK” command to restore the tuple.

2. DROP : Drop is Data Definition Language Statement.

The Drop command removes the table from the database.

With the help of “DROP” command we can drop (delete) the whole structure in one go i.e. it removes the named elements of the schema. By using this command the the table structure and data both is deleted.

Syntax : If we want to drop the table:

DROP table table_name ;  
You can’t restore the table by using the “ROLLBACK” command.

3. TRUNCATE : It is also a Data Definition Language Command (DDL).

It is use to delete all the rows of a table in one go.

With the help of “TRUNCATE” command we can’t delete the single row as here WHERE clause is not used. By using this command the existence of all the rows of the table is lost.

It is comparatively faster than delete command as it deletes all the rows fastly.

Syntax If we want to use truncate :

TRUNCATE table_name;  

You can’t restore the rows of the table by using the “ROLLBACK” command.

Similarity between DELETE, DROP and TRUNCATE

Delete, Drop and Truncate all delete data of table.