chevron_left
358 points
19 13 11

The structured query language is almost the same for MySQL, SQL Server, or other database management systems. Most of the commands are the same and used for the same purpose. In this post, we are going to see the difference between the truncate and delete command in MySQL.


DELETE Command:

The DELETE command is used to delete a row from a table. It is a DML command that stands for Data Manipulation Language. The syntax for using this command is as follows:

DELETE FROM table_name;

You can delete the records based on a condition by using the WHERE clause:

DELETE FROM table_name
WHERE condition;

If you do not specify any condition, the DELETE command removes all the records from the table.


Truncate Command:

The TRUNCATE command is used to delete all the records from a table. It is a DDL command which means Data Definition Language. This command empties a table and no data is left behind. Unlike the DELETE command, you cannot specify a condition to delete the records. The syntax for using this method is as follows:

TRUNCATE TABLE Table_name;

The data deleted by the TRUNCATE command cannot be rolled back in any way.


Difference between DELETE and TRUNCATE Command:

  • The DELETE command is used to delete particular rows and records based on a condition whereas the TRUNCATE command is used to delete all the records from a table.
  • The DELETE is a DML command and TRUNCATE is a DDL command.
  • DELETE command is slower than the TRUNCATE command.
  • DELETE command locks a tuple before removing it whereas the TRUNCATE command locks the data page before removing data.
  • To delete records using the DELETE command, we need DELETE permission whereas to use the TRUNCATE command, we need ALTER permission.
  • DELETE command can be used with indexed views whereas TRUNCATE could not be used with indexed views.

More Posts

Difference between delete and truncate in mysql amna - Jun 28
Difference between truncate and delete and drop Hafsa_Rao - Sep 4
What is the difference between truncate and delete amna - Mar 3
Difference between drop and truncate in sql server Sanjana Sagar - May 28
How to delete duplicate values in SQL sakshi - Jun 29
Differences between MySQL and SQL Server. Sanjana Sagar - May 22
How to delete a table in SQL sakshi - Jul 11
How to delete one row in SQL sakshi - Jun 21
Connect mysql database in java usmanhashmi - Aug 7
Select query in MySQL with where clause sakshi - Jun 26