Open In App

Difference between DELETE, DROP and TRUNCATE

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

1. DELETE : 

Basically, it is a Data Manipulation Language Command (DML). It is used to delete one or more tuples of a table. With the help of the “DELETE” command, we can either delete all the rows in one go or can delete rows one by one. i.e., we can use it as per the requirement or the condition using the Where clause. It is comparatively slower than the TRUNCATE command. The TRUNCATE command does not remove the structure of the table.

  • SYNTAX – 
    If we want to delete all the rows of the table:
DELETE from;
  • SYNTAX – 
    If we want to delete the row of the table as per the condition then we use the WHERE clause,
DELETE FROM table_name WHERE condition; 

Note – Here we can use the “ROLLBACK” command to restore the tuple because it does not auto-commit.

2. DROP : 

It is a Data Definition Language Command (DDL). It is used to drop the whole table. With the help of the “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 existence of the whole table is finished or say lost. 

  • SYNTAX – 
    If we want to drop the table:
DROP table <table_name>;

Note – Here we can’t restore the table by using the “ROLLBACK” command because it auto commits.

3. TRUNCATE : 

It is also a Data Definition Language Command (DDL). It is used to delete all the rows of a relation (table) in one go. With the help of the “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 the delete command as it deletes all the rows fastly. 

  • SYNTAX – 
    If we want to use truncate :
TRUNCATE table <table_name>;

Note – Here we can’t restore the tuples of the table by using the “ROLLBACK” command.

Comparision Between Delete Drop and Truncate

Parameter

Delete

Drop

Truncate

Language

Data Manipulation Language Command (DML)

Data Definition Language Command (DDL)

Data Definition Language Command (DDL)

Purpose

Used to delete content in rows of a table.

Used to delete entire content of table along with the table structure.

Used to delete entire content of table leaving the table structure.

Syntax

DELETE from;(to delete all the rows of the table)
DELETE FROM table_name WHERE condition; (to delete the row of the table as per the condition)

DROP table <table_name>;

TRUNCATE table <table_name>;

ROLLBACK

Can be Rollback

Cannot be Rollback

Cannot be Rollback

Data

Removes specific rows depending upon condition

Removes the entire data immediately.

Removes all rows

Efficiency

Less efficient for large tables as we have to manually specify each and every condition.

Efficiency depends on the size of the object which is being dropped.

More efficient for large tables as we are removing all the data in one step.


Last Updated : 13 Mar, 2024
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads