DELETE vs TRUNCATE vs DROP in SQL: The Real Difference
On this page 9 sections ▾
Three statements that all make data disappear, and one of the most asked comparison questions in SQL interviews. The short version: DELETE removes rows you choose, TRUNCATE removes all of them, and DROP removes the table itself. The differences that actually matter in practice are about what can be undone, what can be filtered, and what happens to everything attached to the table.
Every example below runs, so you can watch a table go from full to empty to gone. The playground rebuilds its data whenever you reload the page, so run them as many times as you like.
The one-line answer
| DELETE | TRUNCATE | DROP | |
|---|---|---|---|
| What it removes | chosen rows | all rows | the table itself |
| Takes a WHERE clause | yes | no | no |
| Table still exists after | yes | yes | no |
| Command family | DML | DDL | DDL |
| Fires row triggers | yes | no | no |
| Can be rolled back | yes, in a transaction | depends on the database | depends on the database |
| Resets auto-increment counters | no | usually yes | not applicable |
| Speed on a large table | slow (row by row) | fast (deallocates pages) | fast |
| Needs which privilege | DELETE | usually ALTER or DROP | DROP |
If you remember one row of that table, make it the WHERE row: it is the difference that decides which statement you can even use for the job in front of you.
DELETE: remove the rows you choose
The only one of the three that can be selective, because the only one of the three that takes a WHERE clause:
DELETE FROM Orders WHERE status = 'Cancelled';
SELECT COUNT(*) AS orders_left FROM Orders;| orders_left |
|---|
| 13 |
● 1 row · produced by running this query on the sample database
Fifteen orders, minus the cancelled ones. DELETE works one row at a time: each removal is written to the transaction log, each row trigger fires, and each row can be rolled back. That is what makes it safe and also what makes it slow — deleting ten million rows this way takes real time and real log space.
With no WHERE clause it empties the table, one logged row at a time:
DELETE FROM Orders;
SELECT COUNT(*) AS orders_left FROM Orders;| orders_left |
|---|
| 0 |
● 1 row · produced by running this query on the sample database
A DELETE with no WHERE clause is the classic career-defining mistake
There is no confirmation prompt and no undo outside a transaction. Before running any DELETE, run a SELECT with the identical WHERE clause and count what comes back — those are exactly the rows that will go.
TRUNCATE: empty the table, keep the table
When the answer to “which rows?” is “all of them”, TRUNCATE does it in one operation instead of a million:
TRUNCATE TABLE Orders;
SELECT COUNT(*) AS orders_left FROM Orders;| orders_left |
|---|
| 0 |
● 1 row · produced by running this query on the sample database
The table is still there, with its columns, its indexes and its permissions intact — it is simply empty. Instead of removing rows one by one, the database deallocates the storage the rows lived in, which is why it is close to instant no matter how large the table was.
What you give up for that speed:
- No WHERE clause. It is all or nothing. If you need to keep some rows, DELETE is your only option.
- No row triggers. Anything that was supposed to happen per deleted row does not happen. Audit tables that rely on triggers will silently miss the event.
- Rollback is not guaranteed. In SQL Server and PostgreSQL a TRUNCATE inside a transaction can be rolled back; in MySQL with InnoDB it commits implicitly and cannot. Check your database before relying on it.
- Identity counters reset. The next inserted row usually starts from 1 again, which matters if anything outside the database remembers old ids.
- Foreign keys can block it. Most databases refuse to truncate a table that another table references, even when the referencing table is empty.
DROP: remove the table itself
Not a way of deleting data — a way of deleting a table:
DROP TABLE Orders;
SELECT COUNT(*) AS products_still_here FROM Products;| products_still_here |
|---|
| 7 |
● 1 row · produced by running this query on the sample database
After that statement there is no Orders table to query at all. Gone with it: the rows, the columns, the indexes, the constraints, the triggers and the permissions granted on it. Any view or procedure that referenced it is now broken.
Reach for DROP when the table should not exist any more — a temporary working table, a migration leftover, a design that changed. Never as a shortcut for emptying one.
IF EXISTS is worth the extra two words
DROP TABLE IF EXISTS Orders succeeds whether or not the table is there, which makes setup and teardown scripts idempotent. The same applies to CREATE TABLE IF NOT EXISTS.
DML and DDL: why the difference matters here
This is the part interviewers are usually probing for. DELETE is DML — data manipulation — so it works inside the normal transaction machinery: it can be rolled back, it fires triggers, it is logged row by row. TRUNCATE and DROP are DDL — data definition — and they operate on the structure rather than the contents.
In several databases, including Oracle and MySQL, DDL causes an implicit commit: the moment you run it, everything you had done in that transaction is committed too, and neither the DDL nor the work before it can be rolled back. That is the single most dangerous property in this whole comparison. The DDL, DML, DCL and TCL guide sorts every SQL statement into its family.
Choosing between them
- Do I need to keep any rows? Yes → DELETE with a WHERE clause. That is the end of the decision.
- Do I want the table to keep existing? Yes → TRUNCATE if triggers and rollback do not matter and nothing references it; DELETE otherwise.
- Should the table stop existing? DROP.
And whichever you choose, on anything that matters: take a backup, wrap it in a transaction if your database allows it, and run the SELECT version first.
Practice this topic
Reading explains the idea; producing it yourself is what makes it stick. These exercises use exactly what this page covers, and your answer is checked by running it against the same sample database:
- Look at the whole product catalogueEasy
- How many customers do we have?Easy
- Revenue actually shippedMedium
Frequently asked questions
What is the difference between DELETE, TRUNCATE and DROP in SQL?
DELETE removes the rows matched by its WHERE clause and leaves the table in place. TRUNCATE removes every row at once and leaves the empty table in place. DROP removes the table itself along with its rows, indexes, constraints and permissions. Only DELETE accepts a WHERE clause.
Is TRUNCATE faster than DELETE?
Yes, usually by a wide margin on large tables. DELETE removes rows one at a time, logging each one and firing triggers, while TRUNCATE deallocates the storage the rows occupied in a single operation. The trade-off is that TRUNCATE cannot be filtered, fires no row triggers, and is not rollback-safe on every database.
Can TRUNCATE be rolled back?
It depends on the database. SQL Server and PostgreSQL allow a TRUNCATE inside a transaction to be rolled back. MySQL with InnoDB treats it as DDL and commits implicitly, so it cannot. Never rely on rolling back a TRUNCATE without checking your specific database first.
Is DELETE DML or DDL?
DELETE is DML, because it changes the data inside a table. TRUNCATE and DROP are DDL, because they change the structure. The practical consequence is that DDL causes an implicit commit in some databases, which ends your transaction whether you wanted it to or not.
Does TRUNCATE reset the auto-increment or identity value?
In most databases, yes: the next inserted row starts numbering again from the beginning. DELETE does not reset the counter, so after deleting every row the next insert continues from where the sequence left off.
How do I delete all rows from a table but keep the table?
Use TRUNCATE TABLE when you want speed and nothing depends on triggers or rollback, or DELETE FROM with no WHERE clause when you need the operation logged, reversible and trigger-firing. Both leave the table itself in place.
Related reading
About the author
SQL Practice
SQL Practice publishes free SQL tutorials, an in-browser playground and 42 practice exercises, built so you can run what you read. Every runnable example in our tutorials is executed against the site’s sample database before it is published. Read how we keep tutorials accurate, or report a mistake.