TRUNCATE vs. DELETE in SQL: Understanding the Differences

Learn how the TRUNCATE and DELETE statements work in SQL, the differences between them, and when you should prefer using one over the other.

When working with database tables, you may have to delete a subset of rows or all the rows. To delete rows from a database table, you can use the SQL TRUNCATE or the DELETE statements depending on the use case.

In this tutorial, we’ll take a closer look at each of the statements, understand how they work, and decide when to prefer using TRUNCATE over DELETE and vice versa.

Before we go any further, it’s helpful to review the following SQL subsets: 

• Data Definition Language (DDL) statements are used to create and manage database objects such as tables. The SQL CREATE, DROP, and TRUNCATE statements are examples of DDL statements.
• Data Manipulation Language (DML) statements are used to manipulate data within database objects. DML statements are used to perform the operations of creating, reading, updating, and delete records.
• Data Query Language (DQL) statements are used to retrieve data from database tables. All SELECT statements fall under the DQL subset.

How to Use the SQL TRUNCATE Statement

Syntax of the SQL TRUNCATE Statement

The syntax to use the SQL TRUNCATE statement is as follows:

TRUNCATE TABLE table_name;

Running the above TRUNCATE command deletes all the rows in the table specified by table_name—and does not delete the table.

The truncate operation does not scan all the records in the table. So it’s reasonably faster when working with large database tables.

SQL TRUNCATE Usage Example

📑 Note: If you have MySQL installed on your machine, you can code along using the MySQL command-line client. You can also follow along in another DBMS of your choice such as PostgreSQL.

Let’s first create a database to work with:

mysql> CREATE DATABASE db1;
Query OK, 1 row affected (1.50 sec)

Then select the database that we just created:

mysql> use db1;
Database changed

The next step is to create a database table. Run the following CREATE TABLE statement to create a simple tasks table:

-- Create the tasks table
CREATE TABLE tasks (
    task_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    due_date DATE,
    status ENUM('Pending', 'In Progress', 'Completed') DEFAULT 'Pending',
    assignee VARCHAR(100)
);

In this example, the tasks table has the following columns:

• task_id: An automatically incrementing unique identifier for each task.
• title: The title or name of the task, limited to 255 characters.
• due_date: The due date for the task, represented as a date.
• status: The status of the task, which can be ‘Pending’, ‘In Progress’, or ‘Completed’. The default value is set to ‘Pending’.
• assignee: The assignee for the particular task.

Now that we’ve created the tasks table, let’s insert records into it:

-- Inserting multiple records into the tasks table
INSERT INTO tasks (title, due_date, status, assignee)
VALUES
    ('Task 1', '2023-08-10', 'Pending', 'John'),
    ('Task 2', '2023-08-11', 'In Progress', 'Jane'),
    ('Task 3', '2023-08-12', 'Completed', 'Mike'),
    ('Task 4', '2023-08-13', 'Pending', 'Alice'),
    ('Task 5', '2023-08-14', 'In Progress', 'Bob'),
    ('Task 6', '2023-08-15', 'Completed', 'Emily'),
    ('Task 7', '2023-08-16', 'Pending', 'David'),
    ('Task 8', '2023-08-17', 'In Progress', 'Olivia'),
    ('Task 9', '2023-08-18', 'Pending', 'Daniel'),
    ('Task 10', '2023-08-19', 'Completed', 'Sophia'),
    ('Task 11', '2023-08-20', 'Pending', 'Matthew'),
    ('Task 12', '2023-08-21', 'In Progress', 'Ava'),
    ('Task 13', '2023-08-22', 'Completed', 'William'),
    ('Task 14', '2023-08-23', 'Pending', 'Ella'),
    ('Task 15', '2023-08-24', 'In Progress', 'James'),
    ('Task 16', '2023-08-25', 'Completed', 'Lily'),
    ('Task 17', '2023-08-26', 'Pending', 'Benjamin'),
    ('Task 18', '2023-08-27', 'In Progress', 'Mia'),
    ('Task 19', '2023-08-28', 'Pending', 'Henry'),
    ('Task 20', '2023-08-29', 'Completed', 'Isabella');

Upon running the insert statement, you should see a similar output:

Query OK, 20 rows affected (0.18 sec)
Records: 20  Duplicates: 0  Warnings: 0

Now run the TRUNCATE table command to delete all the records from the tasks table:

TRUNCATE TABLE tasks;
Query OK, 0 rows affected (0.72 sec)

Doing so removes all records and not the table. You can verify this by running SHOW TABLES; like so:

SHOW TABLES;

+---------------+
| Tables_in_db1 |
+---------------+
| tasks         |
+---------------+
1 row in set (0.00 sec)

And a SELECT query to retrieve data from the tasks table returns an empty set:

SELECT * FROM tasks;
Empty set (0.00 sec)

How to Use the SQL DELETE Statement

Syntax of the SQL DELETE Statement

The general syntax to use the SQL DELETE statement is as follows:

DELETE FROM table_name 
WHERE condition;

The condition in the WHERE clause is the predicate that determines which of the rows should be deleted. The DELETE statement deletes all rows for which the predicate is True.

The DELETE statement, therefore, allows you to have more control over which of the records are deleted.

But what happens when you use the DELETE statement without the WHERE clause?🤔

DELETE FROM table_name;

Running the DELETE statement as shown deletes all the rows in the database table.

If a DELETE statement or a set of DELETE statements are part of an uncommitted transaction,  you can roll back the changes. However, it’s recommended to have your data backed up elsewhere.

SQL DELETE Usage Example

Now let’s see the SQL delete statement in action.

We have deleted all the records from the tasks table. So you can re-run the INSERT statement (that we ran earlier) to insert records:

-- Inserting multiple records into the tasks table
INSERT INTO tasks (title, due_date, status, assignee)
VALUES
    ('Task 1', '2023-08-10', 'Pending', 'John'),
    ('Task 2', '2023-08-11', 'In Progress', 'Jane'),
    ('Task 3', '2023-08-12', 'Completed', 'Mike'),
    ...
    ('Task 18', '2023-08-27', 'In Progress', 'Mia'),
    ('Task 19', '2023-08-28', 'Pending', 'Henry'),
    ('Task 20', '2023-08-29', 'Completed', 'Isabella');

First let’s use the DELETE statement with the WHERE clause. The following query deletes all the rows for which the status is ‘Completed’:

DELETE FROM tasks WHERE status = 'Completed';
Query OK, 6 rows affected (0.14 sec)

Now run this  SELECT query:

SELECT * FROM tasks;

You’ll see that there are 14 rows currently:

+---------+---------+------------+-------------+----------+
| task_id | title   | due_date   | status      | assignee |
+---------+---------+------------+-------------+----------+
|       1 | Task 1  | 2023-08-10 | Pending     | John     |
|       2 | Task 2  | 2023-08-11 | In Progress | Jane     |
|       4 | Task 4  | 2023-08-13 | Pending     | Alice    |
|       5 | Task 5  | 2023-08-14 | In Progress | Bob      |
|       7 | Task 7  | 2023-08-16 | Pending     | David    |
|       8 | Task 8  | 2023-08-17 | In Progress | Olivia   |
|       9 | Task 9  | 2023-08-18 | Pending     | Daniel   |
|      11 | Task 11 | 2023-08-20 | Pending     | Matthew  |
|      12 | Task 12 | 2023-08-21 | In Progress | Ava      |
|      14 | Task 14 | 2023-08-23 | Pending     | Ella     |
|      15 | Task 15 | 2023-08-24 | In Progress | James    |
|      17 | Task 17 | 2023-08-26 | Pending     | Benjamin |
|      18 | Task 18 | 2023-08-27 | In Progress | Mia      |
|      19 | Task 19 | 2023-08-28 | Pending     | Henry    |
+---------+---------+------------+-------------+----------+
14 rows in set (0.00 sec)

Running the following DELETE statement deletes all the remaining 14 records in the table:

DELETE FROM tasks;
Query OK, 14 rows affected (0.20 sec)

And the tasks table is now empty:

SELECT * FROM tasks;
Empty set (0.00 sec)

The SQL DROP Statement

So far, we’ve learned:

• The TRUNCATE statement deletes all the rows from the table.
• The DELETE statement—without a WHERE clause—deletes all the records from the table.

However, the TRUNCATE and DELETE statements do not delete the table. If you want to delete the table from the database, you can use the DROP TABLE command like so:

DROP TABLE table_name;

Now let’s delete the tasks table from the database:

mysql> DROP TABLE tasks;
Query OK, 0 rows affected (0.43 sec)

You’ll see that SHOW TABLES; returns an empty set (as we’ve deleted the only table that was present in the database):

mysql> SHOW TABLES;
Empty set (0.00 sec)

When to Use TRUNCATE vs. DELETE in SQL

To sum up:

• When you need to delete all the rows from a large database table, use the TRUNCATE statement.
• To delete a subset of the rows based on specific conditions, use the DELETE statement.

Summing Up

Let’s wrap up our discussion with a summary:

• When working with database tables, you may want to remove the subset of rows or all the rows in a particular table. To do so you can use the TRUNCATE or the DELETE statements.
• The TRUNCATE statement takes the syntax: TRUNCATE TABLE table_name;. It deletes all the rows in the table specified by table_name but does not delete the table itself.
• The DELETE statement takes the syntax: DELETE FROM table_name WHERE condition;. This removes the rows for which the predicate condition is true.
• Running the SQL DELETE statement without the WHERE clause deletes all the rows in the table. So functionally, this achieves the same result as the SQL TRUNCATE statement.
• Running TRUNCATE is especially faster when working with larger tables, as it doesn’t scan the entire table. So when you need to delete all the rows in a large database table, running truncate can be more efficient.
• When you need to delete a subset of rows—based on a specific condition—you can use the SQL DELETE statement.

For a quick review of commonly used SQL commands, check out this SQL cheat sheet.