What's the difference between DELETE, TRUNCATE, and DROP?
Quick Answer
DELETE removes rows one at a time, optionally filtered by WHERE. It's fully transactional/logged and fires triggers. TRUNCATE deallocates all rows at once by resetting the table's storage. It's much faster and typically resets identity/auto-increment counters, but can't be filtered, and often can't be rolled back (or may implicitly commit) depending on the engine. DROP removes the entire table object — data, schema, indexes, constraints — permanently.
Detailed Answer
All three remove data or structure, but at very different granularities and costs.
DELETE | TRUNCATE | DROP | |
|---|---|---|---|
| Removes | Matching rows (or all rows) | All rows | The whole table object |
Can filter with WHERE | Yes | No | N/A |
| Speed | Slow for large tables (row-by-row logging) | Fast (deallocates pages) | Fast |
| Fires triggers | Yes | Usually no | No |
| Resets identity/auto-increment | No | Usually yes | N/A (table gone) |
| Transactional / rollback-able | Yes, fully | Depends on engine (PostgreSQL: yes; MySQL/InnoDB: implicit commit) | Depends on engine (same caveat) |
| Table structure afterward | Unchanged, empty or filtered | Unchanged, empty | Table no longer exists |
| Category | DML | DDL (in most engines) | DDL |
-- DELETE: row-level, filterable, fully logged
DELETE FROM orders WHERE status = 'cancelled';
-- TRUNCATE: removes everything, resets the table's storage
TRUNCATE TABLE orders;
-- DROP: the table itself is gone
DROP TABLE orders;
Why TRUNCATE is faster
DELETE scans and removes rows one by one, writing a log entry per row (or per page) so it can be rolled back and so triggers can fire. TRUNCATE instead deallocates the data pages that back the table all at once. It's closer to a DDL operation than a DML one, which is why many engines treat it as non-transactional or auto-committing.
- Need to remove a subset of rows, want triggers to fire, or need full rollback mid-transaction? Use
DELETE. - Need to empty a whole table fast (like clearing a staging table between ETL runs) and don't need row-level rollback? Use
TRUNCATE. - Need to remove the table definition entirely, including its indexes and constraints? Use
DROP. - Be careful with
TRUNCATEon tables referenced by foreign keys. Most engines refuse to truncate a table that's the target of an active foreign key unless you cascade or disable the constraint first.