TRUNCATE vs DELETE in SQL — What's the Difference?

DELETE removes rows one at a time and can be filtered and rolled back; TRUNCATE instantly empties a whole table and resets auto-increment counters.

Published September 19, 2026

DELETE removes rows one by one (optionally with WHERE) and is fully logged and rollback-friendly; TRUNCATE empties the whole table quickly, resets auto-increment, and cannot take a WHERE clause.

The concept

DELETE is a DML statement that fires row-level triggers and can target specific rows. TRUNCATE is DDL in MySQL: it drops and recreates the table's data pages, so it is far faster on big tables.

In MySQL, TRUNCATE causes an implicit commit and cannot be rolled back. PostgreSQL and SQL Server allow TRUNCATE inside a transaction.

Comparison

DELETE TRUNCATE
WHERE clause yes no
speed (big table) slow fast
auto-increment kept reset
row triggers fire don't
rollback (MySQL) yes no

Practical examples

Delete some rows

DELETE FROM logs WHERE created_at < '2025-01-01';

Empty the table

TRUNCATE TABLE sessions;

Foreign key blocker

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE child;
SET FOREIGN_KEY_CHECKS = 1;

Common mistakes

  • Running TRUNCATE on production without a backup — no WHERE and often no rollback.
  • Expecting triggers to fire on TRUNCATE.
  • Forgetting DELETE without WHERE removes everything slowly.

Key takeaways

  • DELETE = selective and safe; TRUNCATE = fast full wipe.
  • TRUNCATE resets auto-increment.
  • Check rollback rules for your database.

FAQ

What about DROP TABLE?

DROP removes the table structure too; TRUNCATE keeps the structure.

Can TRUNCATE a table with foreign keys?

Not if other tables reference it, unless constraints are disabled.

More SQL articles