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.
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
TRUNCATE TABLE table_name;
With WHERE clause: DELETE FROM table_name WHERE condition; Without WHERE clause: DELETE TABLE table_name;
Data definition language (DDL)
Data manipulation language (DML)
Deletes all the rows in the database table.
When run without the WHERE clause, the DELETE statement deletes all the records in the database table.
More efficient than the DELETE statement when working with large tables.
Less efficient than the TRUNCATE statement.
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.
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.
Bala Priya C
Bala Priya is a developer and technical writer from India with over three years of experience in the technical content writing space. She shares her learning with the developer community by authoring tech tutorials, how-to guides, and more…. read more
Narendra Mohan Mittal
Narendra Mohan Mittal is a Senior Digital Branding Strategist and Content Editor with over 12 years of versatile experience. He holds an M-Tech (Gold Medalist) and B-Tech (Gold Medalist) in Computer Science & Engineering.
Cloud data warehouses provide a scalable solution for storing and analyzing data in real-time. They enable businesses to reduce costs and improve efficiency by eliminating the need for on-premises hardware and maintenance. Here is our list of the best cloud data warehouse platforms.