What is the difference between TRUNCATE TABLE and DELETE FROM with no WHERE clause?

## Two ways to end up with an empty table `DELETE FROM orders;` and `TRUNCATE TABLE orders;` both leave `orders` with zero rows, and that surface similarity is exactly why the pair is asked so often. Underneath, they are different categories of statement with different guarantees, and picking the wrong one causes real production surprises: a missing audit trail, a primary key that suddenly collides with archived data, or a purge job that cannot be undone. ## Classification: DML vs DDL `DELETE` is **DML** (Data Manipulation Language) — a statement that changes the *contents* of a table, row by row, under the normal transactional rules that apply to `INSERT` and `UPDATE`. `TRUNCATE TABLE` was added to the SQL standard in SQL:2008 as a bulk row-removal statement, and most engines classify and implement it as **DDL** (Data Definition Language) — a statement about the table object rather than about individual rows. That single classification decision explains almost every behavioural difference below. ## WHERE support `DELETE` accepts an arbitrary predicate: ```sql DELETE FROM orders WHERE order_date < DATE '2024-01-01'; ``` `TRUNCATE TABLE` has **no `WHERE` clause at all**. It is all-or-nothing by definition. If you need to remove a subset, `DELETE` is your only option — there is no such thing as a conditional truncate. ## Trigger firing Row-level `DELETE` triggers fire once for each row a `DELETE` removes, so an audit trigger that writes a log row per deletion works exactly as designed. `TRUNCATE` never removes rows individually, so those triggers **do not fire** — the table empties silently as far as your audit table is concerned. PostgreSQL offers a separate statement-level `AFTER TRUNCATE` trigger you can define explicitly, but a plain `AFTER DELETE ... FOR EACH ROW` trigger will not see the truncation in any major engine. ## Identity and sequence counters `DELETE` never touches an identity column's or sequence's counter: delete every row from a table whose last id was 5000 and the next insert is still 5001. `TRUNCATE` typically restarts it. The SQL standard spells the choice out explicitly: ```sql TRUNCATE TABLE staging_events RESTART IDENTITY; TRUNCATE TABLE staging_events CONTINUE IDENTITY; ``` Engines differ on the default and on which options they accept — MySQL resets `AUTO_INCREMENT`, SQL Server resets the identity seed, and PostgreSQL leaves sequences alone unless you write `RESTART IDENTITY`. Never assume; state the option you want where the engine supports it. ## Foreign keys `DELETE` participates in referential integrity normally: `ON DELETE CASCADE` cascades, `ON DELETE SET NULL` nulls the child column, and a plain `NO ACTION`/`RESTRICT` reference raises an error if children remain. `TRUNCATE` generally refuses outright when another table's foreign key references the target, because it is not deleting rows one at a time and cannot run the per-row referential actions. Some engines let you truncate several related tables in one statement or offer a `CASCADE` option that truncates the referencing tables too. ## Speed and logging The practical reason people reach for `TRUNCATE` is speed. `DELETE` must locate and remove each row and record enough information to undo each one, so cost grows with row count; emptying a hundred-million-row table with `DELETE` can run for a long time and generate an enormous amount of undo/redo work. `TRUNCATE` discards the table's contents wholesale and records far less, so it is close to constant-time regardless of size. (The storage and logging mechanics behind that are the engine's business; at the language level the contract is simply "bulk, minimally logged".) ## Rollback This is the most engine-dependent point, and the one candidates most often get wrong by over-generalising. - **PostgreSQL** and **SQL Server**: `TRUNCATE` is transactional. Inside `BEGIN ... ROLLBACK`, the rows come back. - **MySQL** (InnoDB) and **Oracle**: `TRUNCATE` is DDL that triggers an **implicit commit**, so an open transaction is committed and the truncation cannot be rolled back. "TRUNCATE can never be rolled back" is a myth born of MySQL/Oracle experience; "TRUNCATE is always safe inside a transaction" is the opposite error. Know which engine you are on. ## Row counts and privileges `DELETE` reports the number of rows affected, which callers and ORMs often rely on. `TRUNCATE` normally reports nothing useful — if you need the count, count first or use `DELETE`. Truncation also usually demands a stronger privilege than `DELETE` (an explicit `TRUNCATE`, `DROP`, or `ALTER`-level right depending on the engine), which is why a service account allowed to delete rows may still be refused. ## Choosing between them Use `TRUNCATE` for what it is good at: emptying staging and scratch tables between loads, resetting test fixtures, wiping a table whose contents are derived and re-creatable. Use `DELETE` whenever you need a predicate, need triggers to fire, need referential actions applied, need the affected-row count, or need the operation to be undoable on an engine where `TRUNCATE` commits implicitly.