Difference between DELETE vs DROP vs TRUNCATE in SQL

What the differences are between the commands Delete, Drop, and Truncate has to be one of the most popular queries during SQL interviews. We are here to answer any of your questions regarding this interview question. In this article, we will go over this concept and learn the difference between them in a very practical manner. So, what's the wait? let's start!

 

Difference Between DELETE, DROP and TRUNCATE in SQL

Here are key differences between the Truncate, Delete, and Drop commands in SQL:

1. DELETE

Essentially, it is a Data Manipulation Language Command (DML). It is utilized to erase at least one tuple of a table. With the assistance of the "DELETE" order, we can either erase every one of the columns in one go or can erase rows individually. i.e., we can utilize it according to the prerequisite or the condition utilizing the “WHERE” clause. It is nearly slower than the TRUNCATE order.

Working of DELETE command!

DELETE returns the number of lines eliminated from the table.

Nonetheless, DELETE utilizes a row lock during execution and can be rolled back. Here we can utilize the "ROLLBACK" command to regain the tuple in light of the fact that it doesn't auto-commit.

DELETE can be executed by triggers. A trigger can be called before, after, or instead of the DELETE command. Eliminating tuples in another table can also set off (trigger) DELETE.

Obviously, to involve the DELETE order you want DELETE permission for that table.

Syntax:

To delete all the rows from the table “table_name”:

DELETE table table_name;

To delete a particular row as per the condition “COND” from the table “table_name”:

DELETE from table_name WHERE COND;

Example:

Let there be a customer table as follows:

On applying the query “DELETE FROM customers WHERE CustomerName=’Alfreds Futterkiste’”, the information (Tuple) of the customer with the name Alfreds Futterkiste gets deleted.

2. DROP

It is a Data Definition Language Command (DDL), utilized to drop an entire table. With the assistance of the "DROP" order we can drop (erase) the entire design(table) in one go for example it eliminates the named elements of the schema. By utilizing this command, the presence of the entire table is finished or say lost.

The DROP TABLE activity eliminates the table definition and information as well as the lists, imperatives, and triggers connected with the table. This order liberates the memory space. No triggers are terminated while executing the DROP TABLE. This activity can't be moved back in MySQL, yet it can be in Oracle, SQL Server, and PostgreSQL.

In SQL Server, DROP TABLE requires ALTER consent in the composition to which the table has a place; MySQL requires the DROP honor; Oracle requires the DROP ANY TABLE honor. In PostgreSQL, clients can drop their own tables.

Syntax:

If we want to drop the entire table “table_name”:

DROP table table_name

3. TRUNCATE

TRUNCATE TABLE is like DELETE, however, this activity is a DDL (Data Definition Language) order. It likewise erases records from a table without eliminating table construction, yet it doesn't utilize the WHERE statement. Be cautious utilizing this command. TRUNCATE transactions can be rolled back in data set motors like SQL Server and PostgreSQL, but not in MySQL and Oracle.

TRUNCATE is quicker than DELETE, as it doesn't examine each record prior to eliminating it. TRUNCATE TABLE locks the entire table to eliminate information from a table; subsequently, this order likewise utilizes less exchange space than DELETE.

Dissimilar to DELETE, TRUNCATE doesn't return the number of columns erased from the table. It likewise resets the table auto-increase worth to the beginning worth (generally 1). On the off chance that you add a record subsequent to shortening the table, it will have ID=1. Note: In PostgreSQL, you can decide to restart or proceed with the auto-increase esteem. Prophet utilizes a succession to increase values, which isn't reset by TRUNCATE.

Obviously, you really want authorization to utilize TRUNCATE TABLE. In PostgreSQL, you want the honor TRUNCATE; in SQL Server, the base authorization is ALTER table; in MySQL, you want the DROP honor. At long last, Oracle requires the DROP ANY TABLE framework honor to utilize this order.

Syntax:

To truncate the table “table_name”:

TRUNCATE table_name;

Example:

Consider the following customer table:

On running query “TRUNCATE table CUSTOMERS” and then running as follows we get the empty CUSTOMERS table:

Which command is best for which use case?

• To eliminate a particular row: Use the DELETE TABLE command.
• To eliminate all columns from a huge table and leave the table construction, utilize TRUNCATE TABLE. It's quicker than DELETE.
• To eliminate the whole table including its construction and information, use DROP TABLE.

Now that we know about the primary distinctions between TRUNCATE, DELETE and DROP COMMAND in SQL query, do recollect that TRUNCATE and DROP are DDL; though the DELETE command is a DML. This place of contrast between DELETE versus TRUNCATE versus DROP will assist you with evaluating the utilization and further applications of these commands. 

Thus, though you can utilize the DELETE order for eliminating one or various lines from a table, under specific circumstances, you might have to reset a table by utilizing the TRUNCATE command.