DELETE and TRUNCATE both empty a table, and they do it in completely different ways. DELETE is a row-by-row operation that can be filtered, can be logged, and can be rolled back. TRUNCATE is a structural operation that empties the table in one step, usually faster, usually outside the transaction log, and — critically — not available at all in SQLite.
What is the difference between DELETE and TRUNCATE?
Both remove data, and the difference between them is not cosmetic. One is a statement about rows; the other is a statement about the table.
How does DELETE remove rows?
DELETE works one row at a time and honours a WHERE clause. A single refunded ticket is removed:
DELETE FROM tickets WHERE ticket_id = 15;
SELECT COUNT(*) AS tickets_now FROM tickets;
tickets_now
-----------
15
The count falls from sixteen to fifteen. The same statement with a broader condition removes a block — every ticket for the Comedy Cellar:
DELETE FROM tickets WHERE event_id = 4;
SELECT COUNT(*) AS tickets_now FROM tickets;
tickets_now
-----------
12
Three tickets gone, because that event had three.
What does a DELETE with no WHERE clause do?
It empties the table. Run inside a transaction, the damage is visible and then undone:
BEGIN;
DELETE FROM tickets;
SELECT COUNT(*) AS tickets_after_bare_delete FROM tickets;
tickets_after_bare_delete
-------------------------
0
ROLLBACK;
SELECT COUNT(*) AS tickets_after_rollback FROM tickets;
tickets_after_rollback
12
Zero rows, then twelve again. The rollback restored every one of them. In production the equivalent mistake is a DELETE without a transaction around it, and no rollback can bring the rows back — which is why a staging table plus a transaction is the standard defensive pattern for any large deletion.
A DELETE can also take its target from a subquery, which is how you remove rows related to a set you identified elsewhere — every ticket held by somebody who lives in Bristol, say:
DELETE FROM tickets
WHERE holder_id IN (SELECT holder_id FROM ticket_holders WHERE city = 'Bristol');
SELECT COUNT(*) AS tickets_now FROM tickets;
tickets_now
-----------
7
Five more tickets went, all of them belonging to Bristol-based holders. The subquery is evaluated once and its result used as the deletion set.
What does TRUNCATE actually do?
TRUNCATE TABLE venues is supported by MySQL, PostgreSQL, SQL Server and Oracle, and it is not a statement SQLite understands — there is no runnable output to show for it here. The behaviour it produces in those engines is worth knowing precisely, because it is nothing like a DELETE with no WHERE clause:
- It removes every row in one operation rather than row by row, so it is dramatically faster on a large table.
- In MySQL it is a DDL operation and causes an implicit commit — the deletion cannot be rolled back. PostgreSQL and SQL Server can include it in a transaction and roll it back.
- It resets identity columns, so the next AUTO_INCREMENT or IDENTITY value starts over. A DELETE does not.
- It does not fire row-level triggers in most engines, so audit logic on DELETE never runs.
- In MySQL it fails on a table that is referenced by a foreign key; you must remove the constraint first.
That list is the reason TRUNCATE is the wrong tool for a nightly "clear the staging table" job — resetting identity columns and skipping triggers are usually unacceptable side effects. MySQL offers the safer combination TRUNCATE TABLE staging followed by ALTER TABLE staging AUTO_INCREMENT = 1, and every engine offers the simpler answer: DELETE with a WHERE clause inside a transaction.
One more DELETE feature is worth knowing, because it turns a destructive statement into an informative one. SQLite (from version 3.35) and PostgreSQL support a RETURNING clause, which hands back the rows as they are removed:
BEGIN;
DELETE FROM tickets WHERE event_id = 1 RETURNING ticket_id, seat_code;
ROLLBACK;
ticket_id seat_code
--------- ---------
1 A-01
2 A-02
3 B-03
The three rows come back as they are deleted, which is exactly what an audit needs — and because the demonstration runs inside a transaction, it can be rolled back with a full record of what it removed, which is why the ROLLBACK at the end of the statement matters. MySQL has no RETURNING, and SQL Server spells the idea as an OUTPUT clause on the DELETE itself. A second, older trick is DELETE with a LIMIT clause, which MySQL and PostgreSQL support and SQLite does not: it caps how many rows die per statement, which is a crude but real safety valve on a large table.
Which should you reach for?
| Behaviour | DELETE | TRUNCATE |
|---|---|---|
| Row-by-row | yes, one statement per row | no, one structural operation |
| WHERE clause | optional, usually present | never — the whole table goes |
| Rollback | yes, inside a transaction | MySQL no; PostgreSQL and SQL Server yes |
| Identity reset | no | yes |
| Row triggers fire | yes | no, in most engines |
| Speed on a big table | slow, row by row | fast |
| Available in SQLite | yes | no |
The decision rule is short. Delete a subset, or delete rows you might need back: use DELETE inside a transaction. Empty a table permanently, do not need the identifiers to continue, and want it fast: TRUNCATE, after checking whether your engine will let you roll it back.
Quick reference
- DELETE removes rows, one at a time, and honours a WHERE clause and a subquery.
- DELETE without a WHERE clause empties the table — always run it inside a transaction.
- TRUNCATE empties the table structurally, is much faster, and is absent from SQLite.
- TRUNCATE may reset identity columns and skip triggers, which is usually the deciding factor.
- If you might want the rows back, only DELETE inside a transaction can give them to you.
Deletion drill: the ticketing team wants to purge every refunded ticket from the table, but they need the seat codes for the end-of-season audit. Write the query that copies the rows you are about to destroy into a holding table and then removes them, and explain in one sentence why DELETE rather than TRUNCATE is the right choice on that table.