What Is The Difference Between DELETE, DROP And TRUNCATE?

The DELETE, TRUNCATE and DROP commands are very important in SQL. Read through the article to understand the core differences between them.

Srishti Magan

0 min read

What Is The Difference Between DELETE, DROP And TRUNCATE?

0 min read

Table of content:

Difference Between Delete, Drop, and Truncate
What is Delete Command?
What is Truncate Command?
What is Drop Command?

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:

	Delete	Drop	Truncate
Command	Delete is Data Manipulation Command Language (DML)	Drop is Data Definition Command Language (DDL)	Truncate is Data Definition Command Language (DDL)
Use	It is used to delete one or more rows/records from the existing database table.	Drop is used to delete the whole table from the database.	Truncate is used to delete all the rows from a table or entire records from the existing table.
Transition/ Rollback	Here, we can use the “ROLLBACK” command to restore the tuple. If we delete any row from the database, then we can get back that deleted row from the database.	Here, we cannot restore the tuples of the table by using the “ROLLBACK” command. If we use the drop command, then we cannot get back the whole table from using rollback.	Here, we cannot restore the tuples of the table by using the “ROLLBACK” command. If we use the truncate command, then also we cannot get back all the deleted rows.
Memory Space	Delete command does not free the allocated space of the table from memory.	Drop command removes the allocated space of the table from memory.	Truncate command does not free the allocated space of the table from memory.
Performance Speed	Delete command is slower than the Drop command and Truncate command.	Drop command is quicker to perform than the Delete Command but not as fast as compared to the Truncate command.	Truncate command is faster than the Drop command and Delete command.
Integrity Constraints	In the Delete command, Integrity Constraints remain the same.	In the DROP command, integrity constraints will be removed.	In the Truncate command, integrity constraints will not be removed.
Permission	To use Delete, you need DELETE permission on the table.	To use Drop, you need ALTER permission on the schema to which the table belongs and CONTROL permission on the table.	To use Truncate on a table, you need ALTER permission on the table.
Permanency	Doesn't remove the records permanently	Permanently removes records, along with indexes, structures, and privileges	Removes the records permanently
Trigger	Trigger is fired	No	No
Where clause	Can be used	Can't be used	Can't be used
Syntax	`DELETE FROM table_name WHERE condition;`	`DROP TABLE table_name;`	`TRUNCATE TABLE table_name;`

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.

Name	Roll No.
John	101
Ross	102
Rachel	103
Joy	104
Monica	105

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 :

Name	Roll no
Ross	102
Rachel	103
Joy	104
Monica	105

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.

Name	Roll No.
John	101
Ross	102
Rachel	103
Joy	104
Monica	105

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.

Name

Roll No.

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.

Name	Roll No.
John	101
Ross	102
Rachel	103
Joy	104
Monica	105

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:

Edited by

Srishti Magan

Sr. Content Editor

I’m a reader first and a writer second, constantly diving into the world of content. If I’m not writing or reading, I like watching movies and dreaming of a life by the beach.

Tags:

Computer Science