DELETE vs TRUNCATE vs DROP

Hook: Interviewers love this one because the commands look similar, but one can delete rows, one can empty a table fast, and one can make the table disappear entirely.

Question: What is the difference between DELETE, TRUNCATE, and DROP in SQL?

Answer: DELETE removes rows, usually with a WHERE clause, and keeps the table structure. TRUNCATE removes all rows from a table much faster by clearing the table as a whole, not row by row. DROP removes the table object itself, so the table, its data, and usually its indexes and constraints are gone.

Interview-Ready Answer: I think of DELETE as removing specific rows, TRUNCATE as emptying the whole table quickly, and DROP as deleting the table itself. DELETE is row-based and can use WHERE; TRUNCATE is set-based, much faster, and often resets identity columns; DROP removes the object and all its metadata, so you must recreate it before using it again. One important detail is that behavior varies by database, especially for rollback and identity reset.

What they mean in plain words

DELETE is for removing chosen rows. TRUNCATE is for clearing a table completely. DROP is for removing the table itself.

How they work under the hood

1. DELETE: the database finds matching rows, locks the affected rows, writes undo/redo or transaction log records, marks those rows as deleted, and updates indexes. In MVCC databases like PostgreSQL, old row versions may remain until vacuum-like cleanup reclaims space.
2. TRUNCATE: the database takes a strong table lock, deallocates or marks the storage pages as empty, and updates metadata. It does not walk each row one by one, which is why it is usually much faster than deleting millions of rows.
3. DROP: the database removes the table definition from the catalog, drops its dependent storage and usually its indexes and constraints, and makes the object unavailable for future SQL until you recreate it.

When to use each one

• Use DELETE when you want only some rows gone, like old sessions, one customer, or one bad batch.
• Use TRUNCATE when you want a full reset of a table, like staging data, nightly scratch tables, or test fixtures.
• Use DROP when the table itself is no longer needed, such as after a migration or when replacing a schema object.

Performance and locking

DELETE is generally O(n) in the number of affected rows because every row must be processed. TRUNCATE and DROP are usually close to O(1) with respect to row count because they are mostly metadata operations, though dependency checks and catalog work still cost something. On a table with 10 million rows, DELETE can create huge log volume and replication lag, while TRUNCATE is often finished in milliseconds or a short burst of seconds depending on locks and storage engine.

Locking matters too: DELETE usually allows more concurrency than TRUNCATE, while TRUNCATE and DROP often need stronger table-level locks. That means TRUNCATE can briefly block readers and writers, which surprises people who only think about speed.

Important edge cases and database differences

• Rollback: in PostgreSQL, TRUNCATE is transactional and can be rolled back inside a transaction; in MySQL, TRUNCATE is implemented more like DROP plus CREATE and causes an implicit commit.
• Identity / auto-increment: DELETE does not usually reset sequence values. TRUNCATE often resets them, but the exact behavior depends on the database and options like RESTART IDENTITY.
• Foreign keys: TRUNCATE can fail if another table references the table, unless you truncate the dependent tables too or use a cascading option supported by the database.
• Space reclamation: DELETE often leaves reusable free space behind rather than immediately shrinking the file, so disk space may not drop right away.

Bottom line: if you need selective removal, choose DELETE. If you need a fast full reset, choose TRUNCATE. If you want the table gone entirely, choose DROP.

Real-world story

Imagine a checkout service with a table called session_tokens that stores login tokens for customers. A developer wants to clean up expired test data before a release and reaches for the wrong command. If they use DELETE, the table survives and the app keeps working; if they use TRUNCATE, they empty the table but may also hit foreign key restrictions or briefly block traffic; if they use DROP, the table disappears and the login flow starts failing immediately.

What goes wrong: Someone runs DROP TABLE session_tokens in production because they think it is just a fast cleanup. The next sign-in request throws errors like relation does not exist or table not found, dashboards show spikes in 500s, and support tickets mention users being unable to log in. Recovery means restoring the table from backup or redeploying the migration, which is far more painful than a simple row delete.

Follow-up & Tricky Questions

• Can DELETE be rolled back? Usually yes if you are inside a transaction. It is a normal data change, so the database can undo it before commit.
• Does TRUNCATE always reset identity or auto-increment? Not always; it depends on the database. PostgreSQL lets you choose with RESTART IDENTITY, while other systems may reset automatically or behave differently.
• Which one is fastest? For clearing all rows, TRUNCATE is usually the fastest data-removal option, and DROP is also very fast because it removes the object rather than its rows.
• Can I use WHERE with TRUNCATE or DROP? No. Only DELETE supports row filtering with WHERE.
• What happens to indexes and constraints? DELETE keeps them, because the table still exists. TRUNCATE keeps the table definition, while DROP removes the table and its related objects unless the database handles dependencies differently.
• Tricky: Is TRUNCATE just a faster DELETE? No. It is a different operation with different locking, no row filtering, and different foreign key behavior.
• Tricky: Does DROP only remove the data? No. It removes the table itself, so the schema object is gone and queries against it will fail until it is recreated.
• Tricky: Does DELETE immediately return disk space to the OS? Often no. Many databases keep freed space inside the table file for reuse, and separate maintenance is needed to shrink it physically.

Common Mistakes

• Using DROP when you only meant to clear rows: the fix is to use DELETE or TRUNCATE depending on whether you need a filter.
• Assuming TRUNCATE is always safe and instant: it can be blocked by foreign keys and it takes strong locks.
• Forgetting that DELETE may be slow on large tables: if you want to remove everything, TRUNCATE is usually the better tool.
• Assuming identity counters always reset the same way: check your database’s rules; do not guess in production.

Memory Hook

Memory Hook: DELETE picks items out of the box, TRUNCATE empties the box, and DROP throws the box away.

Cheat Sheet

• DELETE = remove selected rows, supports WHERE.
• TRUNCATE = remove all rows fast, no WHERE.
• DROP = remove the table object itself.
• DELETE is usually slower and more row-by-row.
• TRUNCATE and DROP are usually metadata-heavy, not row-heavy.
• Behavior can differ by database, especially rollback, identity reset, and foreign keys.

Practice Tasks

• Create a small table with 5 rows and delete only 2 rows using DELETE.
• Insert rows again, then compare the row counts after TRUNCATE and DROP.
• Add a foreign key between two tables and see why TRUNCATE can fail unless you handle dependencies.