Difference Between Delete, Drop and Truncate Commands in SQL

What are the difference between delete, drop and truncate in SQL?

Structured query languages (SQL) has five different types of commands. Out of them, two types (i.e. Data Definition Language (DDL) and Data Manipulation Language (DML)) are most crucial in SQL.

Drop and truncate are DDL commands. And delete is a DML command. So, which one is suitable for your requirement.

It is common interview or viva question that is asked to students, SQL developer or programmers. So, don’t forget to learn these commands difference.

So, let’s dive into differences.

Delete vs Truncate vs Drop Commands in SQL [in Tabular Form]

#	Content	DELETE Command	TRUNCATE Command	DROP Command
01	Use of SQL Command	‘DELETE’ is SQL command which is used to delete a single or multiple records (or rows) from the existing table based on the given condition.	‘TRUNCATE’ is SQL command which used to delete all records (or rows) .	‘DROP’ command helps to delete all records, table structure, databases, views, triggers.
02	SQL language type	‘DELETE’ is command of data manipulation language (DML).	‘TRUNCATE’ is command of data definition language (DDL).	‘DROP’ command is command of data definition language (DDL).
03	Filter condition	The ‘WHERE’ clause is used in this command to delete specific records.	The ‘WHERE’ clause is not applicable.	The ‘WHERE’ clause is not applicable.
04	Syntax	DELETE FROM table_name [WHERE condition];	TRUNCATE TABLE table_name;	DROP TABLE table_name;
05	Function	After executing ‘DELETE’ command, developer can restore or save data with the help of two SQL commands such as ‘ROLLBACK’ or ‘COMMIT’.	After executing ‘TRUNCATE’ command, developer cannot restore the deleted data from table.	After executing ‘DROP’ command, data and table structure is permannatly deleted from a relational database management system (RDBMS). It can not be rolled back.
06	Operation Speed	The ‘DELETE’ command work slowly as comapred to the ‘TRUNCATE’ and ‘DROP’ commands. Beacuses, it works row by row, applies WHERE clause ,if present before deleteing it.	The ‘TRUNCATE’ command is quicker than ‘DELETE’.	The ‘DROP’ command is quickest compared to ‘DELETE’ and ‘TRUNCATE’.

These three ‘Delete’,’Truncate’, and ‘Drop’ SQL Commands are prominent role at specific relational database management system (RDBMS) such as ORACLE, MYSQL, POSTGRES, many more.

If you have any suggestion to enrich above comparision, please mention in comment below.

Thanks for Reading!

Read more realted Informatica Power Center:

• List of Transformations in Informatica Power Center & IICS | Types
• Active transformation vs Passive transformation in Informatica | Examples
• Filter transformation vs Router transformation in Informatica
• Source qualifier (SQ) transformation and Joiner transformation in Informatica
• Joiner transformation vs Lookup transformation in Informatica