SQL Interview Question: DELETE vs TRUNCATE vs DROP

Short answer: DELETE removes selected rows, TRUNCATE quickly removes all rows but keeps the table, and DROP removes the table itself, including its structure.

DELETE FROM sessions WHERE expires_at < NOW();   -- some rows
TRUNCATE TABLE import_staging;                    -- all rows, table stays
DROP TABLE old_reports;                           -- the whole table is gone

DELETE

• A DML statement; can have a WHERE clause.
• Removes rows one by one and logs each, so it's slow on large tables.
• Fires DELETE triggers; respects foreign keys (and cascades).
• Can be rolled back inside a transaction.
• Doesn't reset auto-increment counters.

TRUNCATE

• Removes all rows by deallocating data pages: very fast, regardless of size.
• No WHERE, and row-level delete triggers don't fire.
• Usually resets auto-increment/identity (PostgreSQL needs RESTART IDENTITY).
• Fails if other tables reference it with foreign keys, unless you use CASCADE (PostgreSQL) or disable checks.
• Transactional in PostgreSQL; in MySQL it causes an implicit commit and can't be rolled back.

DROP

• Removes the table, its data, indexes, constraints and triggers.
• Views and code referencing the table break.

In practice

• Cleaning up old data: DELETE in batches to avoid long locks.
• Emptying a staging or test table: TRUNCATE.
• Removing an unused table: DROP, via a reviewed migration, after a backup.