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
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.