Difference Between Delete, Drop and Truncate Commands in SQL

by

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]

#ContentDELETE Command TRUNCATE Command DROP Command
01Use 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.
02SQL 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).
03Filter conditionThe ‘WHERE’ clause is used in this command to delete specific records.The ‘WHERE’ clause is not applicable. The ‘WHERE’ clause is not applicable.
04SyntaxDELETE FROM table_name
[WHERE condition];		TRUNCATE TABLE table_name;DROP TABLE table_name;
05FunctionAfter 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.
06Operation SpeedThe ‘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:

Are you looking for job?

Job openings »

 

Leave a Comment