DROP vs TRUNCATE vs DELETE
Three commands remove data, and choosing the wrong one can ruin your day. DELETE removes selected rows, TRUNCATE empties a whole table fast, and DROP destroys the table entirely. Knowing exactly what each one does — and whether you can undo it — is essential.
Learn DROP vs TRUNCATE vs DELETE in our free SQL course — a beginner-friendly interactive lesson with runnable queries, a practice exercise and a quick recall.
Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.
By the end you'll pick the right tool every time, understand which are reversible, and know how foreign keys affect them.
What You'll Learn
The Three Commands at a Glance
Memorise this table — it's the heart of the lesson.
1. DELETE — Remove Selected Rows
DELETE removes rows that match a WHERE condition while keeping the table itself. It's a DML statement, so inside a transaction you can roll it back. The big danger: forgetting the WHERE empties the whole table.
DELETE is picking specific files out of a folder and binning them. TRUNCATE empties the folder. DROP deletes the folder itself.
2. TRUNCATE — Empty the Table Fast
TRUNCATE removes every row in one fast operation and keeps the table structure. It doesn't take a WHERE clause, usually resets the auto-increment counter, and is much faster than DELETE on large tables.
3. DROP — Destroy the Table
DROP TABLE removes the table entirely — its rows and its definition. Afterwards the table simply doesn't exist. Guard scripts with IF EXISTS so re-runs don't error.
4. Side by Side
Run through all three on the same table to feel the difference: DELETE trims rows, TRUNCATE empties, DROP annihilates.
Your Turn: pick the right command
Fill in the right keyword for each goal — one removes selected rows, one removes the whole table.
5. Rollback & Recovery
DELETE is DML — inside a transaction you can ROLLBACK and the rows return. DROP and TRUNCATE are DDL and in many engines auto-commit, so they're effectively permanent. PostgreSQL's transactional DDL is a notable exception.
6. Foreign Keys Get in the Way
If another table references this one, the database won't let you drop or truncate it carelessly. Drop the child table first, or use CASCADE (carefully!) to remove dependents along with it.
Reorder Challenge
You want to safely DELETE rows but be able to undo a mistake. Order these steps:
Quick Recall
1) You want to empty a 10-million-row log table as fast as possible. Which command?
TRUNCATE TABLE . It's far faster than DELETE because it doesn't log every row, and it resets the auto-increment counter.
2) Which command can you usually ROLLBACK — DELETE or TRUNCATE?
DELETE (it's DML). TRUNCATE and DROP often auto-commit and can't be rolled back in many engines.
3) After DROP TABLE logs; , what does SELECT * FROM logs do?
It errors — "no such table". DROP removes the table's structure entirely, not just its rows.
Common Errors (and the fix)
- DELETE with no WHERE: empties the entire table. Always double-check the WHERE (preview with a SELECT first).
- Expecting to undo TRUNCATE/DROP: usually impossible. Back up before running them.
- "cannot drop table — other objects depend on it": drop the child table first or use CASCADE .
- WHERE on TRUNCATE: not allowed — TRUNCATE is all-or-nothing. Use DELETE ... WHERE instead.
- Ids not restarting after DELETE: DELETE doesn't reset the counter; TRUNCATE does.
📘 Quick Reference
Syntax
Effect
Frequently Asked Questions
Mini-Challenge: Cleanup Crew
Match the right command to each cleanup goal. Write all three statements and check them in a playground.
🎉 Lesson Complete
- ✅ DELETE ... WHERE removes selected rows; it's reversible in a transaction
- ✅ TRUNCATE empties a whole table fast and resets auto-increment
- ✅ DROP TABLE destroys rows and structure entirely
- ✅ Only DELETE is reliably rollback-able across engines
- ✅ Foreign keys can block removal — drop children first or use CASCADE
- ✅ Next: the Checkpoint — design a full schema from scratch
Practice quiz
Which command removes only the rows that match a condition, keeping the table?
- TRUNCATE
- DROP
- DELETE
- ALTER
Answer: DELETE. DELETE ... WHERE removes selected rows; the table and its structure remain.
Which command empties an entire table fast and cannot take a WHERE clause?
- TRUNCATE
- DELETE
- DROP
- SELECT
Answer: TRUNCATE. TRUNCATE removes every row in one fast, all-or-nothing operation — no WHERE allowed.
What does DROP TABLE do?
- Removes all rows but keeps the table
- Removes one row
- Empties the table and resets ids
- Removes the entire table — rows and structure
Answer: Removes the entire table — rows and structure. DROP destroys the table definition itself, so the table no longer exists.
Why is TRUNCATE usually faster than DELETE on a large table?
- It only deletes half the rows
- It does not log every individual row removal
- It compresses the table first
- It uses an index
Answer: It does not log every individual row removal. TRUNCATE deallocates data pages in bulk without per-row logging, so it is nearly instant.
Which statement can most reliably be undone with ROLLBACK across engines?
- DELETE
- TRUNCATE
- DROP
- None of them
Answer: DELETE. DELETE is DML and rolls back inside a transaction; TRUNCATE/DROP often auto-commit.
Which command typically resets the auto-increment counter back to its start?
- DELETE
- SELECT
- TRUNCATE
- Neither DELETE nor TRUNCATE
Answer: TRUNCATE. TRUNCATE usually resets the identity/auto-increment counter; a plain DELETE does not.
After DELETE removes all rows, where does the next inserted id continue from?
- Always back at 1
- Where the counter left off (not reset)
- A random number
- It errors
Answer: Where the counter left off (not reset). DELETE does not reset the counter, so the next id continues from the previous high value.
After DROP TABLE logs;, what does SELECT * FROM logs do?
- Returns zero rows
- Recreates the table
- Returns all rows
- Errors — no such table
Answer: Errors — no such table. DROP removes the structure entirely, so the table no longer exists to query.
You forget the WHERE clause: DELETE FROM customers; — what happens?
- Nothing — it is a syntax error
- Every row in the table is deleted
- Only the first row is deleted
- The table is dropped
Answer: Every row in the table is deleted. DELETE with no WHERE removes all rows (the table itself is kept).
Another table references customers via a foreign key. What blocks DROP TABLE customers?
- Nothing, it always succeeds
- TRUNCATE is required first
- The dependency — drop the child first or use CASCADE
- The table must be empty
Answer: The dependency — drop the child first or use CASCADE. Referential dependencies block the drop; remove the child table first or use DROP ... CASCADE carefully.