Home » MySQL

Difference between DELETE and TRUNCATE

Learn about the Difference between DELETE and TRUNCATE.
Submitted by Apurva Mathur, on November 24, 2022

MySQL is simply a database management system, the two terms which are important here is database and management. So, the database is a collection of data, we can assume it is a hard copy file but when it comes to manage the data electronically the database comes into the picture. And, the other term is management which means a way to store that crucial data.

DELETE and TRUNCATE both are important concepts in MYSQL. Everyone should have a proper understanding of both concepts. It is also one of the core concepts of the interview and college exam’s purpose. In this article first, we will see the major differences between DELETE and TRUNCATE with the appropriate examples and syntax.

DELETE Statement

Deleting a record is crucial if you filled in some wrong entry or you want to delete some entry permanently then the DELETE statement of MYSQL comes to our rescue. DELETE statement works on data filled in the rows. It helps us to delete data whenever we want to get rid of some useless data. DELETE statements are always used with some WHERE conditions, if you won't write the WHERE condition with this statement then it will delete the entire data of the table.

Syntax:

DELETE FROM table_name
WHERE condition;

Example:

Suppose I have a table named "students" and inside this table, I have the following columns:

Now if I want to delete the record of ID=7 then in such case I can write my query as:

DELETE FROM students
WHERE (`ID` = '7');

As a result, it will delete the data according to the applied condition.

TRUNCATE Statement

The TRUNCATE command is used to delete the entire data in one go without any filtration. The TRUNCATE command deletes the entire data present inside the table except for the table structure. One of the important things here is that if you hit the TRUNCATE command then you can not restore it, once the data is deleted it cannot be rollback. So, it is always suggested that we should have a backup table of every table or database we work on which will prevent losing crucial data.

Syntax:

TRUNCATE TABLE table_name;

Example:

Suppose I have a table named "marks" and inside this table, we have the following columns:

Now if I want to delete the entire data using a single command, then we can write our query as:

TRUNCATE marks;

This query will remove the entire data of the table except for the table structure,

Overview Differences ( DELETE Vs. TRUNCATE)

DELETE	TRUNCATE
DELETE allows you to remove specific data at a time.	TRUNCATE allows you to remove all records from a table.
The command uses DML (Data Manipulation Language).	The command uses DDL (Data Definition Language).
It is important to use where clause here.	Where clause is not used in this command.
You must have authorization to delete it.	Altering a table requires permission.
The performance is much slower when dealing with large databases.	There is no delay, or more accurately, an instantaneous response.
The DELETE operation allows recovering deleted data.	The TRUNCATE operation does not allow recovering deleted data.
A row-by-row deletion of the rows from the table is accomplished by DELETE.	It deletes entire tables of data at a time instead of individual rows.

---