What is the difference between DELETE, TRUNCATE, and DROP?

Q: What is the difference between DELETE, TRUNCATE, and DROP?

**Target Audience:** Beginners who need to remove data safely, and candidates preparing for the engine-specific follow-ups (transactions, locks, identity, space). **Difficulty:** Medium **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page, including a two-session test on PostgreSQL. MySQL and SQL Server behaviour is cited from their manuals. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First Think of a filing cabinet in an office. - **DELETE** is pulling out specific folders, one at a time: "every folder from before February". You can be selective, a clerk writes each removal in the logbook, and if you

The problem

Question presented to candidate: "We need to clear out an audit_log table. A colleague says use DELETE, another says TRUNCATE, and someone suggested just dropping it. What does each one actually do, and how would you choose?"

What a strong answer should cover:

DELETE removes chosen rows (or all, without WHERE), row by row, as normal DML: fires row triggers, checks foreign keys per row, can be rolled back, can return the deleted rows
TRUNCATE removes all rows at once by replacing the table storage: much faster on big tables, gives the space back immediately, fires no ON DELETE triggers, can reset identity counters, and refuses when another table has a foreign key to it
DROP TABLE removes the table itself: rows, definition, indexes, constraints, triggers and grants
Transactions: in PostgreSQL and SQL Server all three can be rolled back; in MySQL TRUNCATE and DROP cause an implicit commit; SQLite has no TRUNCATE at all
Locking and concurrency in PostgreSQL: DELETE takes a row-level lock mode, TRUNCATE an ACCESS EXCLUSIVE lock, and TRUNCATE is not MVCC-safe
After a big DELETE in PostgreSQL the space is only reclaimed by VACUUM

Clarifying questions expected:

Do we need all rows gone, or only old ones (for example older than 90 days)?
Do other tables reference this one, and do triggers or replication depend on row-level deletes?
Is the table being read or written concurrently while we clear it?

Code / implementation expected: Optional — the three statements, and ideally a demonstration of rollback or identity behaviour.

Schema used by the examples:

delete-truncate-drop

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners who need to remove data safely, and candidates preparing for the engine-specific follow-ups (transactions, locks, identity, space). Difficulty: Medium Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page, including a two-session test on PostgreSQL. MySQL and SQL Server behaviour is cited from their manuals.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

Think of a filing cabinet in an office.

DELETE is pulling out specific folders, one at a time: "every folder

The solution is hidden so you can try first

13 min read · 11 sections

Run the code

Edit the query and run it against the tables for this question.

SQLDELETE with WHERE removes chosen rows and can return them

SQLDELETE without WHERE empties the table; DROP removes the table itself

PostgreSQL: TRUNCATE rolls back, and only RESTART IDENTITY resets the counter

CREATE TABLE t_delete (id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, subject text NOT NULL);
CREATE TABLE t_truncate (id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, subject text NOT NULL);
CREATE TABLE t_restart (id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, subject text NOT NULL);
INSERT INTO t_delete (subject) VALUES ('a'), ('b'), ('c');
INSERT INTO t_truncate (subject) VALUES ('a'), ('b'), ('c');
INSERT INTO t_restart (subject) VALUES ('a'), ('b'), ('c');

DELETE FROM t_delete;
TRUNCATE t_truncate;
TRUNCATE t_restart RESTART IDENTITY;
INSERT INTO t_delete (subject) VALUES ('new');
INSERT INTO t_truncate (subject) VALUES ('new');
INSERT INTO t_restart (subject) VALUES ('new');

BEGIN;
TRUNCATE audit_log;
ROLLBACK;

SELECT 1 AS step, 'next id after DELETE' AS what, (SELECT id FROM t_delete) AS value
UNION ALL SELECT 2, 'next id after TRUNCATE', (SELECT id FROM t_truncate)
UNION ALL SELECT 3, 'next id after TRUNCATE RESTART IDENTITY', (SELECT id FROM t_restart)
UNION ALL SELECT 4, 'audit_log rows after TRUNCATE then ROLLBACK', (SELECT COUNT(*)::int FROM audit_log)
ORDER BY step;

PostgreSQL: TRUNCATE ... CASCADE also empties every referencing table

TRUNCATE customers CASCADE;

SELECT
  (SELECT COUNT(*) FROM customers) AS customers,
  (SELECT COUNT(*) FROM orders) AS orders,
  (SELECT COUNT(*) FROM audit_log) AS audit_log;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.