FresherTechnicalSQL
What is the difference between DELETE, TRUNCATE and DROP?
Try answering first
Model answer
All three remove data, but at different levels.
- DELETE removes rows, optionally filtered by
WHERE. It is a DML statement, fires row-level triggers and can be rolled back. It works row by row, so on a big table it is slow and writes a lot of log. - TRUNCATE empties the whole table at once and has no
WHERE. It is much faster because it does not process each row, and it usually resets identity or auto-increment counters. It is DDL: in PostgreSQL and SQL Server it can be rolled back inside a transaction, in MySQL and Oracle it commits implicitly. It also does not fire row-level delete triggers and is blocked by foreign keys that reference the table. - DROP removes the table itself: data, structure, indexes and permissions.
DELETE FROM sessions WHERE expires_at < NOW();
TRUNCATE TABLE staging_events;
DROP TABLE old_reports;
Use DELETE to remove some rows, TRUNCATE to reset a whole table, and DROP only when the table should no longer exist.
Key points
- DELETE is row-level DML with WHERE, triggers and rollback
- TRUNCATE empties the table fast and usually resets identity
- TRUNCATE rollback behavior depends on the engine
- DROP removes the table definition itself
Likely follow-ups
- Why can TRUNCATE fail on a table with foreign keys?
Common mistakes
- Assuming TRUNCATE can always be rolled back
- Using DELETE without WHERE to clear a huge table