DELETE vs DROP vs TRUNCATE in SQL

Why this distinction matters

At a junior data analyst screen at Stripe or DoorDash, the interviewer almost always asks some flavor of "what's the difference between DELETE, DROP, and TRUNCATE?". A junior answers "they all remove data". A senior candidate answers in one sentence — DELETE removes rows, DROP removes the table, TRUNCATE empties the table fast — and then volunteers the rollback story, trigger behavior, and FK trap before being asked. That second version is what separates an offer from a rejection.

Outside the interview room, mixing these up has wrecked production. The three commands look similar in syntax but live in different danger tiers. DROP is irreversible in MySQL with autocommit. TRUNCATE bypasses triggers, so your audit log silently misses the event. DELETE without a WHERE clause keeps the table structure but wipes every row, and on a billion-row table it can take hours and bloat your transaction log.

This post walks through each command, when it fits, what it leaves behind, and the checklist to run before pulling the trigger on production. SQL is Postgres-flavored where dialects diverge — MySQL and SQL Server notes are called out explicitly.

Short answer

DELETE removes rows matching a WHERE clause. The table itself stays, including its structure, indexes, permissions, and auto-increment counter. Triggers fire. Rollback works inside a transaction. It is slow on large tables because every deleted row is written to the WAL or redo log.

DROP removes the table entirely — structure, data, indexes, constraints, permissions, dependent views. The name users no longer exists in the catalog. In MySQL autocommit mode you cannot undo it; in Postgres you can wrap it in a transaction.

TRUNCATE removes all rows but keeps the table object. It is much faster than DELETE because it deallocates data pages rather than deleting row by row. Triggers do not fire (in most engines), the auto-increment resets, and behavior around foreign keys and rollback varies by engine.

Side-by-side comparison

The DDL vs DML distinction matters more than people realize. Because TRUNCATE and DROP are DDL statements in most engines, they take exclusive locks on the table. If you have long-running SELECTs against users, a TRUNCATE will queue behind them and itself block every other query.

DELETE in detail

DELETE is the workhorse for removing specific rows. It is the only one of the three that respects a WHERE clause, which makes it the right answer when you want to keep some rows and drop others.

DELETE FROM users WHERE id = 42;
DELETE FROM users WHERE last_login < '2020-01-01';
DELETE FROM users; -- wipes every ROW, almost always a mistake

Because DELETE is row-by-row, it writes one log entry per deleted row. On a 500-million-row table that can take hours and balloon the transaction log, slowing replication and backups. The standard mitigation is batched deletes:

-- Postgres: batched delete with a CTE, run in a loop
WITH deleted AS (
  DELETE FROM sessions
  WHERE id IN (
    SELECT id FROM sessions
    WHERE created_at < NOW() - INTERVAL '90 days'
    LIMIT 10000
  )
  RETURNING id
)
SELECT COUNT(*) FROM deleted;

DELETE fires BEFORE DELETE and AFTER DELETE triggers per row. If your audit table is fed by a trigger, DELETE populates it — but a chatty trigger can also multiply work tenfold on small tables.

TRUNCATE in detail

TRUNCATE empties a table by deallocating its data pages, which is dramatically faster than DELETE for any non-trivial size. It exists for the case where you want a clean slate without touching the schema.

TRUNCATE TABLE staging_orders;

Behavior differs across engines in ways that matter. In Postgres, TRUNCATE is transactional and can be rolled back. It skips row-level triggers but fires statement-level ones. By default it blocks if other tables reference the target via FKs; TRUNCATE TABLE users CASCADE empties dependents too. In MySQL, TRUNCATE is DDL and implicitly commits — no ROLLBACK, and it simply blocks if any FK references the table. In SQL Server, TRUNCATE can be rolled back inside a transaction, triggers do not fire, and permissions are preserved.

The auto-increment counter resets to 1 after TRUNCATE in all major engines. Occasionally desirable for staging tables, almost always a problem for any table referenced by external systems — once an order_id of 1001 has been emailed to a customer, you do not want a new order to reuse that id three weeks later.

DROP in detail

DROP removes the database object entirely. After DROP TABLE users, the name no longer exists in the catalog and any view, FK, or stored procedure that referenced it becomes invalid.

DROP TABLE users;
DROP DATABASE mydb;
DROP INDEX idx_users_email;
DROP VIEW active_users;
DROP TABLE IF EXISTS temp_import_2026_05;

IF EXISTS turns "table does not exist" from an error into a no-op, which matters when DROP is part of a migration that might run twice.

Recovery for DROP is bleak. In MySQL with autocommit, it is gone the moment the statement returns. In Postgres a DROP inside an explicit transaction can be rolled back, but most production workflows do not wrap a DROP in BEGIN; ... COMMIT;. Real recovery options are a logical backup (pg_dump, mysqldump), a storage-layer snapshot (RDS, Cloud SQL), or point-in-time recovery from WAL archives. If none exist, the table is gone — the asymmetric blast radius that makes DROP the most feared of the three.

Worked examples by scenario

-- Nightly cleanup of expired sessions: DELETE in batches
DELETE FROM sessions WHERE created_at < NOW() - INTERVAL '30 days';

-- Remove test accounts from a bug bash: DELETE with precise WHERE
DELETE FROM users
WHERE email LIKE '%@test.example.com'
  AND created_at > '2026-05-01';

-- Nightly reload of a staging table from a vendor feed: TRUNCATE + COPY
BEGIN;
TRUNCATE TABLE staging_orders;
COPY staging_orders FROM '/import/orders.csv' WITH (FORMAT csv);
COMMIT;

-- Tear down a one-off experiment table: DROP with IF EXISTS guard
DROP TABLE IF EXISTS experiment_2026_q1_recommender;

-- Rotate an aged-out partition (Postgres declarative partitioning)
ALTER TABLE events DETACH PARTITION events_2024_01;
DROP TABLE events_2024_01;

The pattern: DELETE when you need a predicate, TRUNCATE when you want every row gone but the table to stay, DROP when you want the table itself gone.

Safe operating procedures

Treat these statements on production with the same gravity as a deploy. Before any DROP, confirm nothing depends on the object. In Postgres the catalog has the answer:

-- Views that reference the table
SELECT viewname FROM pg_views WHERE definition ILIKE '%users%';

-- Foreign keys pointing in
SELECT conrelid::regclass AS table_name, conname
FROM pg_constraint
WHERE confrelid = 'users'::regclass;

-- Snapshot first
CREATE TABLE users_backup_20260518 AS SELECT * FROM users;

Before a DELETE, run the predicate as a SELECT first to see how many rows you are about to touch, then wrap the DELETE in a transaction:

SELECT COUNT(*) FROM orders WHERE status = 'abandoned' AND created_at < '2026-01-01';

BEGIN;
DELETE FROM orders WHERE status = 'abandoned' AND created_at < '2026-01-01';
SELECT COUNT(*) FROM orders; -- sanity check before COMMIT
COMMIT;

Before a TRUNCATE, snapshot if the table is anything other than purely transient:

SELECT COUNT(*) FROM staging_orders;
CREATE TABLE staging_orders_bk AS SELECT * FROM staging_orders;
TRUNCATE TABLE staging_orders;

Foreign keys: the silent trap

Foreign keys turn each of these commands into a different beast. The same DELETE FROM users WHERE id = 42 may delete one row, ten thousand rows, or fail outright depending on the FK declaration.

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id) ON DELETE CASCADE
);

-- This also wipes every order for user 42
DELETE FROM users WHERE id = 42;

-- Postgres: TRUNCATE also empties dependents
TRUNCATE TABLE users CASCADE;

ON DELETE CASCADE is convenient and quietly dangerous — many fraud and audit incidents trace back to a CASCADE that no one remembered. ON DELETE RESTRICT (the default in most engines) is safer because the DELETE fails loudly when dependent rows exist. MySQL refuses to TRUNCATE a table any FK references at all. DROP cascades through DROP TABLE users CASCADE, removing the table and silently dropping FK constraints on every referencing table — those tables remain but referential integrity is gone. Almost never the right move on production.

Common pitfalls

The most expensive pitfall is DELETE without a WHERE. It looks like a normal DELETE, runs without error, and quietly removes every row. The fix is procedural rather than syntactic: never run a DELETE in a production console without wrapping it in BEGIN; ... COMMIT; and running a SELECT COUNT(*) between the two statements.

The second pitfall is forgetting that TRUNCATE skips triggers. Teams build audit pipelines that depend on AFTER DELETE triggers writing to an event table, then use TRUNCATE for performance and wonder six months later why the audit log is missing entire days of activity. Either move audit logic out of triggers into the application layer, or use DELETE even when TRUNCATE would be faster.

The third pitfall is the silent CASCADE. ON DELETE CASCADE makes a DELETE on one parent row delete every row in every child table that references it — sometimes a thousand-to-one amplification. The fix is to know which of your FKs cascade and which restrict, ideally in a generated diagram kept in the repo.

The fourth pitfall is running TRUNCATE in production without a backup because "this is just a staging table". Staging tables become source-of-truth tables the moment someone writes a downstream report against them, and downstream consumers rarely tell you when they start depending on your data. Snapshot before any destructive operation on a table other humans can see.

The fifth pitfall is assuming DROP can be rolled back. In Postgres it can, but only inside an explicit transaction, and most people DROP from a SQL console without BEGIN;. In MySQL with autocommit, the table is gone the moment Enter is pressed. Build the muscle to type BEGIN; first as a free undo button.

Interview cheat sheet

Lead with the one-sentence summary, then volunteer the next detail before being asked: DELETE removes rows (WHERE supported), TRUNCATE empties the table (no WHERE), DROP removes the table itself. Speed: DROP fastest, TRUNCATE fast, DELETE slow. Rollback: DELETE always inside a transaction; TRUNCATE rolls back in Postgres but not MySQL; DROP same caveat. Triggers: DELETE fires them, TRUNCATE skips them, DROP is N/A. Auto-increment: DELETE keeps it, TRUNCATE resets it, DROP destroys it with the table.

If the interviewer pushes for production stories, describe the standard BEGIN; ... SELECT COUNT(*); COMMIT; ritual. Senior interviewers want to hear rituals, not just syntax.

Related reading

• SQL window functions interview questions
• NULL in SQL guide
• CTE with SQL guide
• COALESCE in SQL guide

If you want to drill SQL questions like this every day, NAILDD is launching with 500+ SQL problems covering exactly this kind of "junior trap" interview pattern.

FAQ

Can I undo a DROP TABLE?

In Postgres, only if the DROP was inside an explicit transaction that has not yet committed — BEGIN; DROP TABLE users; ROLLBACK; recovers the table. In MySQL with default autocommit, no. Your recovery path is the most recent logical backup, a managed-service snapshot, or point-in-time recovery from WAL archives. If none of those are set up, the table is permanently lost.

Is TRUNCATE always faster than DELETE?

Without a WHERE clause, yes — TRUNCATE deallocates pages instead of deleting rows one at a time. With a selective WHERE, a DELETE backed by a good index can finish in milliseconds while TRUNCATE is not even an option (it has no WHERE). The right comparison is "DELETE all rows" vs "TRUNCATE", and there TRUNCATE wins by orders of magnitude on large tables.

When should I use DROP CASCADE?

Almost never in production. DROP TABLE users CASCADE drops users and also drops every foreign-key constraint that references it — those referencing tables stay, but their referential integrity is silently gone. Legitimate use cases are tearing down test databases, dropping experiment tables, and migrations where you are intentionally rebuilding the dependency graph.

What's the safest way to clear a staging table?

TRUNCATE TABLE staging_xyz inside a transaction is the standard choice if your engine supports transactional TRUNCATE (Postgres, SQL Server). In MySQL, where TRUNCATE auto-commits, the conservative pattern is DELETE FROM staging_xyz inside a transaction so you can ROLLBACK — slower, but safer. Either way, snapshot first if anyone downstream might read the table.

Does DELETE shrink the table on disk?

No, not directly. DELETE marks rows as dead but does not reclaim disk pages. In Postgres you need VACUUM (or VACUUM FULL for full reclamation). In MySQL InnoDB the pages are reused by future inserts but the file does not shrink without OPTIMIZE TABLE. This is why TRUNCATE is preferred when you want to actually reclaim disk.

Why does TRUNCATE reset auto-increment but DELETE does not?

TRUNCATE is effectively a metadata reset of the table contents, and the auto-increment counter is part of that metadata. DELETE is row-level and never touches the counter, on the assumption you may still want to reference deleted ids or keep strictly increasing ids for new inserts. Never trust auto-increment to be monotonic across TRUNCATE.