Anushkakhatri — Published On May 31, 2022 and Last Modified On June 3rd, 2022
Beginner Data Engineering Database SQL
This article was published as a part of the Data Science Blogathon.

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);

 

Commands in SQL

 

Use the SHOW TABLES statement to check the tables present in the current database:
Commands in SQL

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;

 

Commands in SQL

 

 

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;

 

Commands in SQL
It shows that 3 rows are affected by this statement.

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

 

Commands in SQL

 

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;

 

Commands in SQL

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

 

Commands in SQL

 

 

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;

 

Truncate command

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

Truncate Command

 

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;

Drop command

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.

 

Difference Between DELETE, TRUNCATE and DROP Commands

DELETE TRUNCATE DROP
1.      DELETE is a Data Manipulation Language (DML) command. TRUNCATE is a Data Definition Language (DDL) command. DROP is a Data Definition Language (DDL) command.
2.     The DELETE command lets us either delete all the rows or delete them one by one, and the ‘WHERE’ clause allows us to implement it according to the requirement. The TRUNCATE command cannot be used to delete a single row as there is no ‘WHERE’ clause in this case. The DROP command destroys the existence of the whole table.
3.     The DELETE command does not require dropping the table to delete all records from a table; it simply removes each record individually. The TRUNCATE command first drops the table and then recreates it to delete all records from a table. Rows of records cannot be dropped using the DROP command.
4.     Integrity constraints will not be removed with the DELETE command. Integrity constraints will not be removed with the TRUNCATE command. Integrity constraints are removed with the DROP command.
5.     The DELETE command does not free the tablespace from memory. The TRUNCATE command does not free the tablespace from memory. DROP release memory from the tablespace.
6.     The DELETE command deletes each record individually, making it slower than a TRUNCATE command. The TRUNCATE command is faster than both DROP and DELETE commands. DROP is quick to execute but slower than TRUNCATE because of its complexities.
7.     Data can be rolled back with the DELETE command. Data cannot be rolled back with the TRUNCATE command. Data cannot be rolled back with the DROP command since it permanently deletes the table.

 

Conclusion

In this article, we have discussed the DELETE, TRUNCATE and DROP commands, and we have used the Employee_detail table to explain how these commands work and that they have different effects on the table, its structure, and its attributes. Here are the significant points to remember from this article:

  • DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command.
  • If you want to clear the tablespace from memory, you should use the DROP command.
  • The TRUNCATE command is the fastest of all.
  • Data cannot be rolled back after using TRUNCATE and DROP commands, so use them carefully.

 I hope this article has helped you clarify the difference between DELETE, TRUNCATE and DROP commands, so you know where and when to use them.

The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion.

About the Author

Our Top Authors

Download Analytics Vidhya App for the Latest blog/Article

Leave a Reply Your email address will not be published. Required fields are marked *