How TRUNCATE Differs from DELETE and DROP

The short answer: DELETE removes rows (all of them or those matching WHERE), TRUNCATE instantly empties the whole table, DROP removes the table itself along with its structure and data. The full picture of the differences is in the table:

Now let's look at each command and the nuances interviewers love to ask about.

DELETE: Removing Rows by Condition

DELETE is the only one of the three that can remove selectively:

MySQL 8.1
-- delete canceled orders older than a year
DELETE FROM orders
WHERE status = 'canceled' AND order_date < '2025-06-12';

-- delete all rows (the table remains)
DELETE FROM orders;

DELETE is a data-level command (DML). The DBMS processes rows one by one: it checks the condition, writes every removed row to the log, fires ON DELETE triggers. Two consequences follow:

• The deletion can be rolled back. Until the transaction is committed, ROLLBACK puts all rows back.
• It is slow on large tables. Removing ten million rows can take minutes and bloat the transaction log.

DELETE does not touch the auto-increment counter. If you delete all rows and insert a new one, numbering continues where it left off:

MySQL 8.1
CREATE TABLE logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message VARCHAR(100)
);

INSERT INTO logs (message) VALUES ('first'), ('second'), ('third');
DELETE FROM logs;
INSERT INTO logs (message) VALUES ('after DELETE');

SELECT id, message FROM logs;

After DELETE FROM users a new row was inserted and got id = 501 instead of 1. Why?

TRUNCATE: Instantly Emptying a Table

MySQL 8.1
TRUNCATE TABLE logs;

TRUNCATE is a structure-level command (DDL). Instead of removing rows one by one, the DBMS simply releases the space the table occupies — as if the table were recreated from scratch. That is why TRUNCATE completes in a fraction of a second on a million rows and on a billion alike.

The speed comes at the cost of restrictions:

• No WHERE condition — only the entire table at once.
• ON DELETE triggers do not fire: no row-by-row deletion happens, so any "when a row is deleted, do X" logic is silently skipped.
• In MySQL TRUNCATE cannot be rolled back. Like any DDL command, it implicitly commits the current transaction. In PostgreSQL TRUNCATE is transactional: inside BEGIN ... ROLLBACK the data comes back.

In MySQL the auto-increment counter resets to one:

MySQL 8.1
TRUNCATE TABLE logs;
INSERT INTO logs (message) VALUES ('after TRUNCATE');

SELECT id, message FROM logs;

In PostgreSQL the sequence keeps going by default — to reset it you have to ask explicitly:

PostgreSQL 17.5
TRUNCATE TABLE logs RESTART IDENTITY;

DROP: Removing the Table Entirely

MySQL 8.1
DROP TABLE logs;

-- won't fail with an error if the table is already gone
DROP TABLE IF EXISTS logs;

DROP destroys the table completely: data, structure, indexes, triggers, constraints. After it there is nowhere to insert data "back" — the table has to be created again with CREATE TABLE.

DROP is for when the table is no longer needed at all: temporary tables, obsolete parts of the schema, leftovers of experiments. If the goal is just to get rid of the data, DROP is overkill: along with the data you lose privileges, indexes and everything that referenced the table.

The Pitfall: Foreign Keys

If other tables reference a table via foreign keys, TRUNCATE refuses to run — even when the referencing tables are empty. MySQL:

MySQL 8.1
TRUNCATE TABLE authors;

PostgreSQL gives the same error but immediately suggests the way out:

MySQL 8.1
TRUNCATE TABLE authors;

In PostgreSQL you can empty both tables with a single command — TRUNCATE books, authors — or add CASCADE, which also empties every referencing table. Be careful with CASCADE: it removes data from tables you may have forgotten even exist.

DELETE in the same situation works row by row and fails only if a specific row being deleted is referenced.

What to Choose: Three Typical Scenarios

• Remove part of the data by condition — only DELETE. Also the choice when you need triggers or rollback in MySQL.
• Fully empty a table before reloading data — TRUNCATE: fast, space is released immediately, the counter can be reset. Typical cases are log tables and staging tables before a re-import.
• Get rid of a table for good — DROP.

A statistics table with 200 million rows is fully reloaded every night. Which command is best for emptying it before the load?

The Interview Question

"Can TRUNCATE be rolled back?" is a favorite trick question. The correct answer depends on the DBMS: in PostgreSQL — yes, TRUNCATE obeys transactions and ROLLBACK brings the data back. In MySQL and Oracle — no: TRUNCATE implicitly commits the transaction, and the data can only be recovered from a backup. Answering "no, never" or "yes, always" invites follow-up questions from the interviewer.

What's Next

• the syntax and subtleties of DELETE — in the lesson Deleting data, the DELETE operator;
• why DDL commands cannot be rolled back in MySQL — in the lesson Transactions;
• cement the difference in practice — in the SQL trainer.