The differences between the DELETE and TRUNCATE SQL commands
As a developer, you most likely have used these commands (DELETE and TRUNCATE) when dealing with databases in your applications, or have at least come across these terms somewhere.
These commands can be a bit confusing when you are a beginner, where you don't know which to use and how they differ from each other.
In this article, I will help clear that out with aid of some examples.
For a better understanding, let's begin by creating a sample MySQL database with one table that contains several records (rows) for demonstration.
Below is a phpMyAdmin screenshot of our database named "test" with one table named "users".
And below is the "users" table with 5 records.
In the "users" table, we have set the primary key field (id) as an AUTO_INCREMENT integer value. If we insert a new record to the table, the id for it will be 6. If we add another after that, the id will be 7 and so on.
1. The DELETE command
The DELETE is a Data Manipulation Language(DML) command used to delete a single or multiple existing record(s) in a database table.
Syntax
DELETE FROM table_name WHERE condition;
The table_name stands for the name of the database table we want to delete from, while condition represents a condition that must be met in order for the deletion to happen. The condition always comes after the WHERE keyword.
Example
DELETE FROM users WHERE last_name = 'Khan';
Below is our "users" table after running the above statement. The record with "Khan" as the last_name has been deleted and is no longer present among the table records. If we had multiple records with "Khan" as the last_name, they would also have all been deleted.
We can as well write the DELETE statement without the WHERE clause. If we omit it, then all the records in the table will be deleted, leaving the table empty.
DELETE FROM users;
Note: It's very crucial to use the WHERE clause in the DELETE statement in order to specify which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted.
The execution of the DELETE command returns the number of rows removed from the table.
If we insert new records into our "users" table after deletion, the AUTO_INCREMENT id column will continue from where the id of the last record had reached and not from 1 again.
Alternatively from deletion through SQL statements, you can also delete records through the phpMyAdmin GUI by clicking on the Delete button on the record you want to delete. For multiple records, all you have to do is to check/tick all the records you want to delete in the left column of the table, then click on the Delete button at the bottom of the table.
The database user is required to have at least the DELETE permission in order to execute the DELETE command.
2. The TRUNCATE command
The TRUNCATE TABLE is a Data Definition Language(DDL) command used to delete all the rows in a database table.
Syntax
TRUNCATE TABLE table_name;
Unlike the DELETE command, the TRUNCATE command does not contain a WHERE clause. So when using it you cannot specify which records to delete. It removes all the rows from a table at once
The TRUNCATE command is applicable when deleting all the records from the table. It is also much faster than the DELETE command.
Example
TRUNCATE TABLE users;
The above statement deletes all the table records in a similar way to the DELETE command (without the WHERE clause), only that it resets the AUTO_INCREMENT counters on the table.
If we insert new records into our "users" table after executing the TRUNCATE command, the AUTO_INCREMENT id column will start afresh beginning from 1.
This is because the TRUNCATE command resets the AUTO_INCREMENT counters on the table.
MySQL truncates the table by dropping and creating the table.
The database user is required to have at least the ALTER permission in order to execute the TRUNCATE command.
If you want to truncate a MySQL database table manually through the phpMyAdmin GUI, then follow this procedure:
- Open the database table you want to truncate in phpMyAdmin.
- Click on the Operations tab at the top of the table.
- Scroll to the bottom at the section labeled Delete data or table.
- Click on the link labeled Empty the table (TRUNCATE).
The differences between the DELETE and TRUNCATE commands
| DELETE | TRUNCATE |
|---|---|
| Delete is a DML(Data Manipulation Language) command. | Truncate is a DDL(Data Definition Language) command. |
| Used to delete one or more specified records (through use of the WHERE clause), or all records in a table. | It is used to delete all the records in the table (does not support the WHERE clause). |
| Being a DML command, it can be rolled back. | Being a DDL command, it cannot be rolled back. |
| It is executed using a row lock mechanism where each row in the table is locked for deletion. | It is executed using a table lock mechanism where the whole table is locked to remove all records. |
| Scans every row before deleting it, thus it deletes one record at a time. | Deletes all records at once, it doesn't scan every record before removing it. |
| The DELETE command is much slower compared to TRUNCATE. | The TRUNCATE command is much faster. |
| To use the DELETE command the database user is required to have at least the DELETE permission. | The database user is required to have at least the ALTER permission to use this command. |
| This command can be used with indexed views. | Cannot be used with indexed views. |
| It does not reset AUTO_INCREMENT counters. | TRUNCATE resets the AUTO_INCREMENT counters on the table. |
The Take-Away
Both commands can be used to delete records from a database table.
If you want to delete all the records in a table, it is preferable to use TRUNCATE instead of DELETE as the former is much faster than the latter.
If you want to delete only specific records from a table, use the DELETE command with a WHERE clause.
