What's the difference between DELETE, TRUNCATE, and DROP?
Quick Answer
`DELETE` removes rows one at a time (optionally filtered by `WHERE`), is fully transactional/logged, and fires triggers. `TRUNCATE` deallocates all rows at once by resetting the table's storage, is much faster, 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 individually, writing an entry to the transaction/redo log per row (or per page) so it can be rolled back and so triggers can fire per row. TRUNCATE instead deallocates the data pages that back the table wholesale — 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 it fully rollback-able mid-transaction →
DELETE. - Need to empty a whole table fast (e.g., clearing a staging table between ETL runs) and don't need row-level rollback →
TRUNCATE. - Need to remove the table definition entirely, including its indexes and constraints →
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 from another table unless you cascade or disable the constraint first.