Comparison of DELETE, TRUNCATE and DROP Commands in SQL

Introduction

As part of performing operations on the Table, we often need to remove records or remove the entire table from the database, so we can use the DELETE, TRUNCATE, and DROP commands. People often get confused about which statement to use when they need to DELETE, TRUNCATE or DROP. So, here in this article, we will discuss the differences between the DELETE, TRUNCATE, and DROP commands in MySQL and provide an example to help us understand the differences.

DELETE

MySQL’s DELETE command is one of the most powerful commands for eliminating unnecessary rows or data from a table, and it is a Data Manipulation Language command. Using the Delete query in MySQL, you can delete multiple rows from a table in a single query, which comes in handy when you need to delete many records from a table. This command does not affect the table’s attributes and structure, and the row data cannot be recovered once deleted. Making a backup of the database before deleting it is therefore strongly recommended so that you can restore it later if necessary.

Syntax of DELETE command:

To Delete all records:

DELETE  FROM table_name;

To delete records based on conditions:

DELETE FROM table_name WHERE condition;

If the WHERE clause is missing from the query statement, all records in the table will be deleted. Otherwise, it specifies which record should be deleted based on the conditions provided.

TRUNCATE

MySQL’s TRUNCATE command removes complete data, i.e. all rows, from the table and does not alter the table’s structure. It is a Data Definition Language command. It is not possible to roll back the data after using this command, and you cannot retrieve the contents of the table with a flashback command. The TRUNCATE command drops and recreates the table, thus making it very efficient.

Syntax of TRUNCATE command:

TRUNCATE TABLE table_name;

In TRUNCATE, there is no WHERE clause used; the command deletes all the table’s records.

DROP

MySQL’s DROP command removes one or more tables from the database and their structures, attributes, and constraints. The data cannot be rolled back with this command since it permanently deletes the table and releases memory from the tablespace. It is impossible to drop a parent table with a foreign key constraint, and first, we have to remove the foreign key constraint or drop the child table.

Syntax of DROP command:

DROP TABLE table_name;

Example demonstrating the use of DELETE, TRUNCATE and DROP

In the following example, we will demonstrate the use of DELETE, TRUNCATE, and DROP commands:

Let’s create the Employee_detail table with EmployeeID as the primary key:

>> CREATE TABLE Employee_detail(

EmployeeID INT NOT NULL PRIMARY KEY,

FirstName varchar(20),

LastName varchar(20),

City varchar(20),

Salary INT);

 

Insert the values into the Employee_detail table, then use the SELECT command to view the contents:

>> INSERT INTO Employee_detail(EmployeeID, FirstName, LastName, City, Salary)

VALUES(1001, ‘Rahul’, ‘Sharma’, ‘Delhi’, 20000),

(1002, ‘John’, ‘Smith’, ‘Mumbai’, 10000),

(1003, ‘Eva’, ‘Jackson’, ‘Bangalore’, 15000),

(1004, ‘Lily’, ‘Mathew’, ‘Mumbai’, 10000),

(1005, ‘Jay’, ‘Gill’, ‘Delhi’, 5000),

(1006, ‘Tom’, ‘Ford’, ‘Bangalore’, 10000),

(1007, ‘Ananya’, ‘Sharma’, ‘Bangalore’, 30000),

(1008, ‘Rini’, ‘Verma’, ‘Mumbai’, 20000);

>> SELECT * FROM Employee_detail;

DELETE command with WHERE condition:

We want to delete the records where the salary of an employee exceeds 15000 here:

>> DELETE FROM Employee_detail WHERE Salary > 15000;

 

Using the below select statement, we can view the records of the table after deleting the records with salaries greater than 15000:

>> SELECT * FROM Employee_detail

 

DELETE command without WHERE condition:

If we use the DELETE command without the WHERE clause, it will delete all the records in the Employee_detail table, and we can cross-check this with the SELECT command, which shows that we have no records in our table.

>> DELETE FROM Employee_detail;

>> SELECT * FROM Employee_detail;

 

Using the SHOW TABLE statement, we can see that the DELETE query does not affect the Employee_detail table’s structure.

 

TRUNCATE command:

The TRUNCATE command removes complete records from the table, and we can cross-check this with the SELECT command, which shows that we have no records in our table.

>> TRUNCATE Employee_detail;

>> SELECT * FROM Employee_detail;

 

Using the SHOW TABLE statement, we can see that the TRUNCATE query does not affect the Employee_detail table’s structure.

DROP command:

DROP command removes the Employee_detail table from the database, and we can cross-check this with the SELECT command, which shows that the Employee_detail table does not exist.

>> DROP TABLE Employee_detail;

>> SELECT * FROM Employee_detail;

The DROP command eliminates the entire existence of a table, so the SHOW TABLE statement shows that there is no Employee_detail table in the database.