DROP vs TRUNCATE vs DELETE

Reviewed & published by Brayan K

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.

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

• Remove specific rows with DELETE ... WHERE
• Empty an entire table fast with TRUNCATE
• Destroy a table completely with DROP
• Which commands you can roll back
• Why TRUNCATE resets auto-increment
• How foreign keys block or chain removal

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.

-- The table this lesson uses. Run this block first.
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
    id              INTEGER PRIMARY KEY,
    name            TEXT,
    last_order_date TEXT
);
INSERT INTO customers VALUES
    (1, 'Ava',  '2023-06-14'),
    (2, 'Ben',  '2024-03-02'),
    (3, 'Cara', '2023-11-30'),
    (4, 'Dev',  '2024-08-21');

-- DELETE removes ROWS that match a condition. The table stays.
DELETE FROM customers
WHERE last_order_date < '2024-01-01';

-- Only the matching rows go; the table and its structure remain, and
-- DELETE is DML, so inside a transaction it can be rolled back.
SELECT * FROM customers ORDER BY id;

-- ⚠️ FORGET THE WHERE and you delete EVERY row:
--   DELETE FROM customers;   -- empties the table (but keeps the table)
-- Left commented out so the rows above survive for you to look at.

-- ✅ Expected result:
-- id | name | last_order_date
-- 2 | Ben | 2024-03-02
-- 4 | Dev | 2024-08-21

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.

-- engine: postgres — from here the lesson needs a PostgreSQL server.
-- SQLite has no TRUNCATE at all (its equivalent is DELETE FROM t), and
-- this site's Run button is SQLite in your browser, so it will answer
-- "near TRUNCATE: syntax error". That is the engine, not the snippet.

DROP TABLE IF EXISTS logs;
CREATE TABLE logs (id SERIAL PRIMARY KEY, message TEXT);
INSERT INTO logs (message) VALUES ('boot'), ('request'), ('shutdown');

-- TRUNCATE empties the WHOLE table fast, with no WHERE clause.
TRUNCATE TABLE logs;

-- 0 rows, structure intact. Much faster than DELETE on a big table
-- because it does not journal every row individually.
-- It also RESETS the identity counter, which a plain DELETE does not:
-- this new row gets id 1 again, not 4.
INSERT INTO logs (message) VALUES ('after truncate');
SELECT * FROM logs;

-- ✅ Expected result:
-- id | message
-- 4 | after truncate

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.

-- DROP destroys the ENTIRE table — rows AND structure.
DROP TABLE logs;

-- The table is gone: SELECT * FROM logs now fails with
-- 'relation "logs" does not exist'. This is DDL and is permanent.
--
-- Running DROP twice errors, so guard it when you are not sure:
DROP TABLE IF EXISTS logs;

-- Ask the catalogue rather than trusting the comment — no row means
-- no table:
SELECT tablename FROM pg_tables WHERE tablename = 'logs';

-- ✅ Expected result:
-- tablename

4. Side by Side

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

-- The whole picture, side by side, counting the rows after each step.
DROP TABLE IF EXISTS products;
CREATE TABLE products (id INT PRIMARY KEY, name TEXT);
INSERT INTO products VALUES (1, 'Mug'), (2, 'Kettle'), (3, 'Pan');

CREATE TEMP TABLE steps (step TEXT, rows_left INT);

DELETE FROM products WHERE id = 2;              -- one row, by condition
INSERT INTO steps SELECT 'after DELETE WHERE id = 2', count(*) FROM products;

DELETE FROM products;                            -- every row, no WHERE
INSERT INTO steps SELECT 'after DELETE (no WHERE)', count(*) FROM products;

INSERT INTO products VALUES (4, 'Bowl');         -- put something back
TRUNCATE TABLE products;                         -- everything, fast
INSERT INTO steps SELECT 'after TRUNCATE', count(*) FROM products;

SELECT * FROM steps;

-- DELETE and TRUNCATE both leave the table standing. DROP does not:
--   DROP TABLE products;  -> the table no longer exists at all

-- ✅ Expected result:
-- step | rows_left
-- after DELETE WHERE id = 2 | 2
-- after DELETE (no WHERE) | 0
-- after TRUNCATE | 0

Your Turn: pick the right command

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

-- 🎯 YOUR TURN — fill in the blanks
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (id INT PRIMARY KEY, status TEXT);
INSERT INTO orders VALUES (1, 'paid'), (2, 'cancelled'), (3, 'paid'), (4, 'cancelled');
CREATE TABLE IF NOT EXISTS temp_import (id INT);

-- Goal 1: remove ONLY the cancelled orders, keeping the table.
___ FROM orders WHERE status = 'cancelled';   -- 👉 replace ___ with DELETE

-- Goal 2: completely remove a temporary table, structure and all.
___ TABLE temp_import;                        -- 👉 replace ___ with DROP

-- Get both right and the paid orders are still here, while
-- temp_import has stopped existing:
SELECT id, status FROM orders ORDER BY id;

-- ✅ Expected result:
-- id | status
-- 1 | paid
-- 3 | paid

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.

-- Can you undo it? That depends on the statement AND the engine.
DROP TABLE IF EXISTS accounts;
CREATE TABLE accounts (id INT PRIMARY KEY, balance NUMERIC);
INSERT INTO accounts VALUES (1, 0), (2, 50);

CREATE TEMP TABLE undo (what TEXT, rows_after INT);

BEGIN;                                   -- start a transaction
DELETE FROM accounts WHERE balance = 0;  -- ...realise that was a mistake...
ROLLBACK;                                -- undo it — the row comes back
INSERT INTO undo SELECT 'DELETE then ROLLBACK', count(*) FROM accounts;

BEGIN;
TRUNCATE TABLE accounts;
ROLLBACK;
INSERT INTO undo SELECT 'TRUNCATE then ROLLBACK', count(*) FROM accounts;

SELECT * FROM undo;

-- Both rows survive BOTH rollbacks here, because in PostgreSQL TRUNCATE
-- is fully transactional. That is worth knowing precisely, because it is
-- NOT universal:
--   PostgreSQL, SQL Server : TRUNCATE rolls back like any other statement
--   MySQL (InnoDB), Oracle : TRUNCATE forces an implicit COMMIT, so by the
--                            time you type ROLLBACK the table is already empty
--   SQLite                 : no TRUNCATE at all — use DELETE FROM t
-- DROP is DDL: in PostgreSQL it also rolls back, in MySQL and Oracle it
-- does not. When in doubt, take a backup rather than trust the transaction.

-- ✅ Expected result:
-- what | rows_after
-- DELETE then ROLLBACK | 2
-- TRUNCATE then ROLLBACK | 2

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.

-- Foreign keys can block (or chain) these operations.
DROP TABLE IF EXISTS line_items;
DROP TABLE IF EXISTS baskets;
CREATE TABLE baskets (id INT PRIMARY KEY);
CREATE TABLE line_items (
    id        INT PRIMARY KEY,
    basket_id INT REFERENCES baskets(id)
);
INSERT INTO baskets VALUES (1);
INSERT INTO line_items VALUES (10, 1);

-- line_items references baskets, so this FAILS while that reference stands:
--   DROP TABLE baskets;
--   ❌ cannot drop table baskets because other objects depend on it
-- Left commented out, because it would stop the block here.

-- Two ways forward. Drop the child first, which is the safe one:
DROP TABLE line_items;
DROP TABLE baskets;

-- Or, in Postgres, take the dependants with it — powerful and dangerous,
-- because it silently drops things you did not name:
--   DROP TABLE baskets CASCADE;

SELECT count(*) AS tables_left
FROM pg_tables
WHERE tablename IN ('baskets', 'line_items');

-- ✅ Expected result:
-- tables_left
-- 0

Reorder Challenge

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

A.   COMMIT;          -- (or ROLLBACK if it looks wrong)
B.   BEGIN;
C.   SELECT COUNT(*) FROM orders WHERE status = 'cancelled';   -- preview
D.   DELETE FROM orders WHERE status = 'cancelled';

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.

Mini-Challenge: Cleanup Crew

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

-- 🎯 MINI-CHALLENGE
-- You're cleaning up a database. Write the RIGHT command for each goal:
--   1. Remove every row from "session_cache" quickly and reset its ids
--   2. Delete only the expired rows from "tokens" (expires_at < CURRENT_DATE)
--   3. Permanently remove the obsolete "old_reports" table entirely
--
-- ✅ Expected: a TRUNCATE, a DELETE ... WHERE, and a DROP — one each.

-- your three statements here

🎉 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

Continue this course

• Previous: Constraints (PK, FK, UNIQUE, CHECK)
• Next: Checkpoint: Building a Schema — Design a normalized schema with constraints — then a quiz
• Quick reference: SQL cheat sheet › Modifying Data

Frequently asked questions

What's the core difference between DELETE, TRUNCATE, and DROP?

DELETE removes specific rows (or all rows) but keeps the table and is a DML statement you can roll back. TRUNCATE quickly empties the entire table, keeps its structure, usually resets auto-increment, and is harder to undo. DROP removes the entire table — both its rows and its definition — so the table no longer exists at all.

Why is TRUNCATE faster than DELETE?

DELETE removes rows one at a time and logs each deletion so it can be rolled back, which is slow on large tables. TRUNCATE deallocates the data pages in bulk without logging individual rows, so it's nearly instant regardless of table size. The trade-off is that TRUNCATE can't use a WHERE clause and is much harder to roll back.

Can I roll back a DROP or TRUNCATE?

It depends on the engine. PostgreSQL supports transactional DDL, so DROP and TRUNCATE inside an explicit transaction can be rolled back. MySQL (with the default storage engine) and many others auto-commit DDL, so once you DROP or TRUNCATE, it's effectively permanent. Never assume you can undo them — back up first.

Does DELETE reset the auto-increment counter?

No. After a DELETE the next inserted row continues from where the counter left off, even if the table is now empty. TRUNCATE, by contrast, typically resets the auto-increment/identity counter back to its starting value. If you need ids to restart, TRUNCATE (or explicitly reset the sequence) rather than DELETE.