The DELETE and the TRUNCATE both empty a table, and they do it in completely different ways. The DELETE is a row-by-row operation that can be filtered, that can be logged, and that can be rolled back. The TRUNCATE is a structural operation that empties the table in one step. It is usually faster, it is usually outside the transaction log, and SQLite does not have it at all.
The previous lesson in this module covered "How Does UPDATE Change Rows Without Collateral Damage?", and the next lesson in this module is about "How Do Upserts Insert or Update in One Step?".
What is the difference between the DELETE and the TRUNCATE?
Both of them remove the data, and the difference between them is not cosmetic. One of them is a statement about the rows, and the other one is a statement about the table.
How does the DELETE remove the rows?
The DELETE works one row at a time and it honours a WHERE clause. Here a single refunded ticket is removed.
DELETE FROM tickets WHERE ticket_id = 15;
SELECT COUNT(*) AS tickets_now FROM tickets;tickets_now
-----------
15The count falls from sixteen to fifteen.
The same statement with a broader condition removes a block. That is every ticket for the Comedy Cellar.
DELETE FROM tickets WHERE event_id = 4;
SELECT COUNT(*) AS tickets_now FROM tickets;tickets_now
-----------
12Three tickets are 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 it is undone.
BEGIN;
DELETE FROM tickets;
SELECT COUNT(*) AS tickets_after_bare_delete FROM tickets;tickets_after_bare_delete
-------------------------
0ROLLBACK;
SELECT COUNT(*) AS tickets_after_rollback FROM tickets;tickets_after_rollback
-----------
12There are zero rows, and then there are 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. That 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, and that is how you remove the rows that are related to a set that you identified somewhere else. That is 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
-----------
7Five more tickets went, and all of them belonged to the Bristol-based holders. The subquery is evaluated once and its result is used as the deletion set.
What does the TRUNCATE actually do?
The statement TRUNCATE TABLE venues is supported by MySQL, PostgreSQL, the SQL Server and Oracle. SQLite does not understand it, so there is no runnable output to show for it here.
The behaviour that 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 it causes an implicit commit, so the deletion cannot be rolled back. PostgreSQL and the SQL Server can include it in a transaction and roll it back.
- It resets the identity columns, so the next AUTO_INCREMENT or IDENTITY value starts over. A DELETE does not.
- It does not fire the row-level triggers in most of the engines, so the audit logic on the DELETE never runs.
- In MySQL it fails on a table that is referenced by a foreign key, and you have to remove the constraint first.
That list is the reason the TRUNCATE is the wrong tool for a nightly "clear the staging table" job. The resetting of the identity columns and the skipping of the triggers are usually the unacceptable side effects.
MySQL offers the safer combination of the TRUNCATE TABLE staging followed by the ALTER TABLE staging AUTO_INCREMENT = 1, and every engine offers the simpler answer, which is the DELETE with a WHERE clause inside a transaction.
There is one more DELETE feature worth knowing, because it turns a destructive statement into an informative one. SQLite from version 3.35 and PostgreSQL support a RETURNING clause, and it 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-03The three rows come back as they are deleted, and that is exactly what an audit needs. Because the demonstration runs inside a transaction, it can be rolled back with a full record of what it removed, and that is why the ROLLBACK at the end of the statement matters.
MySQL has no RETURNING, and the SQL Server spells the idea as an OUTPUT clause on the DELETE itself.
There is a second and older trick, which is the DELETE with a LIMIT clause. MySQL and PostgreSQL support it and SQLite does not. It caps how many rows die per statement, and that is a crude but real safety valve on a large table.
Which one 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. You delete a subset, or you delete rows that you might need back, and then you use the DELETE inside a transaction. You empty a table permanently, you do not need the identifiers to continue, and you want it fast, and then you use the TRUNCATE, after checking whether your engine lets you roll it back.
Quick reference
- The DELETE removes the rows, one at a time, and it honours a WHERE clause and a subquery.
- The DELETE without a WHERE clause empties the table, so you always run it inside a transaction.
- The TRUNCATE empties the table structurally, it is much faster, and it is absent from SQLite.
- The TRUNCATE may reset the identity columns and skip the triggers, which is usually the deciding factor.
- If you might want the rows back, then only the 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. You write the query that copies the rows that you are about to destroy into a holding table and then removes them. Then you explain in one sentence why the DELETE rather than the TRUNCATE is the right choice on that table.