TRUNCATE vs DELETE vs DROP

Compare DELETE, TRUNCATE, and DROP to understand exactly what each one removes and how they differ.

Introduction

MySQL gives you three different ways to remove data, and they are easy to confuse because they can look similar on the surface: DELETE, TRUNCATE, and DROP. Each one removes something different, at a different scale, with different consequences.

Understanding the difference matters — using the wrong one can silently destroy far more than you intended, or fail to reset things you expected to be reset.

What You Will Learn

What DELETE, TRUNCATE, and DROP each actually remove.
How each one affects the table structure and the auto-increment counter.
Which of them can be rolled back inside a transaction.
A worked example showing the auto-increment difference between DELETE and TRUNCATE.

DELETE Recap

As covered in the previous lesson, DELETE removes rows matching a WHERE condition (or all rows, if WHERE is omitted). It keeps the table structure intact and can be rolled back if run inside a transaction that has not yet been committed. Because it checks each row individually against the condition, it is also the slowest of the three for removing large amounts of data.

DELETE FROM students WHERE marks < 40;

TRUNCATE

TRUNCATE TABLE removes every row from a table in one fast operation. It does not accept a WHERE clause — it always removes all rows, no exceptions.

TRUNCATE TABLE students;

Unlike DELETE, TRUNCATE also resets the table's AUTO_INCREMENT counter back to its starting value, and it cannot be rolled back the same way inside a transaction on most storage engines, since it works by quickly recreating the table rather than deleting rows one at a time. The table structure — its columns, types, and constraints — remains exactly as it was.

TRUNCATE Has No WHERE Clause

TRUNCATE always removes every row. There is no way to truncate "some" rows — for a conditional removal, you must use DELETE instead.

DROP

DROP TABLE is the most destructive of the three. It removes the entire table — its data, its structure, its columns, its indexes, everything — permanently.

DROP TABLE students;

After this statement runs, the "students" table no longer exists at all. Querying it afterward would produce an error saying the table does not exist, because there is nothing left to query.

DROP Removes the Table Itself

DROP does not just empty a table — it deletes the table's definition entirely. If you wanted to keep the empty table around for future use, DROP is the wrong choice; use TRUNCATE instead.

Comparison Table

Aspect	DELETE	TRUNCATE	DROP
What it removes	Rows matching a condition (or all rows)	All rows, always	The entire table structure and data
Supports WHERE?	Yes	No	No
Speed	Slower (row by row)	Fast	Fast
Table structure kept?	Yes	Yes	No — table is gone
Auto-increment counter	Unaffected	Reset to start	N/A (table gone)
Rollback inside a transaction	Yes	Generally no	Generally no

Worked Example: Auto-Increment

Suppose "students" has an auto-incrementing id column, and it currently contains three rows with ids 1, 2, and 3.

-- Delete all rows using DELETE
DELETE FROM students;

-- Insert a new row
INSERT INTO students (name, marks) VALUES ('Neha', 82);

SELECT * FROM students;

Result After DELETE + INSERT

+----+------+-------+
| id | name | marks |
+----+------+-------+
| 4  | Neha | 82    |
+----+------+-------+

Even though the table was emptied, the new row got id 4 — DELETE does not reset the auto-increment counter, so it continues right where it left off.

-- Now compare with TRUNCATE
TRUNCATE TABLE students;

INSERT INTO students (name, marks) VALUES ('Neha', 82);

SELECT * FROM students;

Result After TRUNCATE + INSERT

+----+------+-------+
| id | name | marks |
+----+------+-------+
| 1  | Neha | 82    |
+----+------+-------+

After TRUNCATE, the very next insert got id 1 again — TRUNCATE resets the auto-increment counter back to its starting point, while DELETE never does.

Common Mistakes

Avoid These Mistakes

Using DROP when you only meant to empty the table — DROP removes the structure permanently, requiring you to recreate it from scratch.
Expecting TRUNCATE to accept a WHERE clause — it cannot filter, it always removes every row.
Assuming TRUNCATE can be rolled back inside a transaction just like DELETE — on most storage engines it cannot.
Forgetting that DELETE keeps the auto-increment counter running, which can surprise you if you expected ids to restart from 1.

Best Practices

Use DELETE when you need to remove specific rows based on a condition.
Use TRUNCATE when you want to quickly empty an entire table but keep its structure for future use.
Use DROP only when you are certain you want the table gone permanently.
Always back up important data before running TRUNCATE or DROP, since both are difficult or impossible to reverse.

Frequently Asked Questions

Which is fastest for removing all rows?

TRUNCATE is generally the fastest, since it does not check rows individually or log each row deletion the way DELETE does.

Can I use TRUNCATE with a WHERE clause?

No. TRUNCATE always removes every row in the table. If you need conditional removal, use DELETE instead.

Does TRUNCATE delete the table itself?

No. TRUNCATE empties the table but keeps its structure — columns, types, and constraints all remain. Only DROP removes the table itself.

Can DROP be undone?

Not without a backup. DROP permanently removes the table and all of its data; there is no built-in recovery.

Key Takeaways

DELETE removes rows matching a condition, keeps the structure, and does not reset auto-increment.
TRUNCATE removes all rows quickly, keeps the structure, and resets auto-increment.
DROP removes the entire table — structure and data — permanently.
DELETE can typically be rolled back inside a transaction; TRUNCATE and DROP generally cannot.

Summary

DELETE, TRUNCATE, and DROP all remove data, but at very different scales and with very different consequences. DELETE is selective and reversible inside a transaction; TRUNCATE is fast and resets auto-increment but is not easily reversible; DROP removes the table itself, permanently.

In this lesson, you compared all three and saw how auto-increment behaves differently between DELETE and TRUNCATE. Next, you will learn aggregate functions, which summarize data across many rows at once.

Lesson 30 Completed

You can explain the difference between DELETE, TRUNCATE, and DROP.
You know how each one affects the auto-increment counter.
You know which of the three can be rolled back inside a transaction.
You are ready to learn aggregate functions.

Next Lesson →