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.

Memorise this table — it's the heart of the lesson.

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.

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.

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.

Run through all three on the same table to feel the difference: DELETE trims rows, TRUNCATE empties, DROP annihilates.

Fill in the right keyword for each goal — one removes selected rows, one removes the whole table.

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.

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.

You want to safely DELETE rows but be able to undo a mistake. Order these steps:

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.

Match the right command to each cleanup goal. Write all three statements and check them in a playground.

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.