What is the difference between DELETE, TRUNCATE, and DROP?
DELETE removes selected rows and can use a WHERE clause, and it can be rolled back. TRUNCATE removes all rows quickly, cannot use WHERE, and resets the table, and it is harder or impossible to roll back in many databases. DROP removes the entire table including its structure, so the table no longer exists.
- DELETE removes selected rows and can be rolled back, and it can use a WHERE clause.
- TRUNCATE quickly removes all rows but keeps the table structure.
- DROP removes the entire table, including its structure.
Quick comparison
- DELETE: removes some or all rows, supports WHERE, logs each row, can be rolled back.
- TRUNCATE: removes all rows fast, no WHERE, keeps the table structure.
- DROP: removes the whole table, its data, and its structure.
DELETE FROM users WHERE active = 0; -- remove some rows
TRUNCATE TABLE logs; -- empty the table fast
DROP TABLE temp_data; -- remove the table entirelyDELETE is a DML command that works row by row, while TRUNCATE and DROP are DDL commands that act on the whole table structure.
Group them clearly: DELETE for selective and reversible removal, TRUNCATE for a fast full wipe, DROP to remove the table itself. Noting that DELETE is DML while TRUNCATE and DROP are DDL earns bonus credit.
Frequently asked questions
Is TRUNCATE faster than DELETE?
Yes. TRUNCATE removes all rows in one operation without logging each row, while DELETE processes and logs rows individually, which is slower.
Can you roll back a TRUNCATE?
It depends on the database. In some, like SQL Server inside a transaction, you can, while in others like MySQL it commits immediately.
Common follow up questions
Related interview questions
Want the full SQL guide?
Read every SQL concept with notes, diagrams, and code in one place. Track your progress as you go.
Open the SQL guide All SQL questions