What Is The Difference Between DELETE, DROP And TRUNCATE?

One of the most infamous questions during SQL interviews has to be the difference between Delete, Drop and Truncate commands. Though all three commands are used to remove records from a table, they differ from each other in the extent of their capabilities.

We're here to answer all your queries about this interview question. Read ahead to understand the differences and each command in detail. 

Difference Between Delete, Drop, and Truncate

Let's look at the difference between delete, drop, and truncate. We will discuss the basic difference in this table: 

Find the advantages and disadvantages of SQL here. 

What is Delete Command?

The Delete command in SQL is a part of the Data Manipulation Language command (DML command), a sub-language of SQL that allows modification of data in databases.

In SQL, the Delete command (Data Manipulation command) is used to delete one or more rows/records from the existing database table. A delete statement is used to delete the selected number of existing records from the table. DELETE maintains an entry in the transaction log for each row deleted.

With the delete statement, we have to give a condition with where clause. That condition decides which row is going to be deleted from the existing table. All in all, delete gives us the ability to perform particular deletion of records as per the user's need.

While using the delete statement we don't require the list of column names but only a condition is required to execute it. It does not remove the table structure.

Syntax

Delete Command Syntax is as follows:

DELETE FROM TableName
	 WHERE condition;

Example

Here, let's take the example of the table named STUDENT.

In this student table, there are multiple columns. But we have to remove only the data of that student whose roll number is 101. Here, the where clause filters records according to the given condition.

DELETE FROM Student
	 WHERE Rollnumber = 101;

If we run the DELETE SQL Command, only that row will be deleted. This will not affect the entire table going forward. To execute a DELETE SQL Command, delete permissions are required on the target table.

How does DELETE work?

Let's take a detailed example to understand the process behind the delete query. Here, we have a sample table.

If we execute the below SQL Query, then we observe that a single record is removed from the table student.

DELETE FROM Student
	 WHERE Roll number = 101;

Then, after execution, the row where the roll number equals 101 and the student name equals John will be removed.

The table will be as follows :

We can see delete changes entire table structure.

What is Truncate Command?

The Truncate command in SQL is a part of the Data Definition Language command (DDL command), a sub-language of SQL that allows modification of data in databases.

In SQL, the Truncate command is used to delete all the rows from a table or entire records from the existing table. Truncate is used to delete the whole date from the table. It resets the table identity.

With the Truncate command, we don't have to give any condition with a where clause. We only require the table name.

Truncate is faster performance-wise. It does not require checking any condition like delete. Here, all the rows, or in other words, entire records are removed from the existing table. What only remains is the column names of the table.

We cannot roll back the data after using the TRUNCATE table statement.

Syntax

Truncate Command Syntax is as follows:

TRUNCATE TABLE table_name;

Example

Here, let's take the example of the table named STUDENT.

In this student table, there are multiple columns (Attribute Of Table Student). The values are stored in the form of rows in the table.

TRUNCATE TABLE Student;

If we run the TRUNCATE query, then only the column name will remain, and all the existing rows will be removed immediately from the student table. And this will affect the entire table.

How does Truncate work?

Let's take a detailed example to understand the process behind the Truncate table statement. Here, we will see that truncate resets table identity.

If we execute the below SQL Query,

TRUNCATE TABLE Student;

Then, after execution, all the rows will be removed from the student table

The table will be empty but not deleted from the database.

What is Drop Command?

The Drop command in SQL is a part of the Data Definition Language command (DDL command).

DROP Command removes the definition of the table, all the data, indexes, triggers, constraints, and permission specifications of that table.

In SQL, the Drop command is used to delete the existing table. Drop Command drops the whole table from the existing Relational Database Management System (RDBMS).

With the Drop command, we don't have to give any condition with a where clause. We only require the table name.

We cannot roll back the data after using the DROP command. It removes the whole table structure.

Syntax

Drop Command Syntax is as follows:

DROP TABLE table_name;

Example

Here, let's take the example of the sample table named Employee.

In this Employee table, there are multiple columns (Attribute Of Table Employee), and many rows are stored as well.

DROP TABLE Employee;

If we run the DROP table query, then the entire table will be permanently removed from the database. Once a table is dropped, then we cannot get it back. There is no option for a rollback after executing the drop command.

How does DROP work?

Let's take a detailed example to understand the process behind the Drop query. Here is the sample table, table employees, and its dummy records.

If we execute the below SQL Query

DROP TABLE Employees;

Then, after execution of the drop query, the whole employee table will be removed from the database. The table will not be found in the existing database.

So this query will not only delete all the records from the Employees table, but it will remove the existence from the database itself.

Summing up...

To sum up, the DELETE command removes specific rows from a table based on conditions. TRUNCATE is used to delete all rows from a table and release the allocated storage space. It cannot be rolled back. DROP is used to permanently delete a table or database object, including associated data and objects, and it cannot be recovered without a backup. We hope this article helped you understand the differences between these commands well. So, the next time you appear for your SQL interviews, we are sure you will rock it!

You might also be interested in reading:

1. 10+ JavaScript Projects For Beginners
2. Difference Between Active And Passive Cyber Attacks Explained
3. Compiler Vs Interpreter: What's the Difference?
4. Difference Between Sensor And Transducer Explained!
5. Difference Between DELETE and TRUNCATE Commands In SQL