The short answer
All three remove data, but they remove different things and behave differently.
DELETE removes rows one by one, and you can choose which rows with a WHERE clause. It is logged row by row, so it can be rolled back inside a transaction, and it fires triggers.
DELETE FROM orders WHERE status = 'cancelled';TRUNCATE removes all rows from a table at once, with no WHERE clause. It is much faster on big tables, because it does not log every row. The table structure stays. It usually resets auto-increment counters. Whether it can be rolled back depends on the database.
TRUNCATE TABLE staging_orders;DROP removes the whole table: the rows, the structure, the indexes and the permissions. After it, the table no longer exists.
DROP TABLE staging_orders;| DELETE | TRUNCATE | DROP | |
|---|---|---|---|
| Removes | chosen rows | all rows | the table itself |
| WHERE allowed | yes | no | no |
| Speed on a big table | slow | fast | fast |
| Table structure kept | yes | yes | no |
Which command group they belong to: DELETE is a DML (data manipulation) command. TRUNCATE and DROP are DDL (data definition) commands, and many databases commit DDL immediately.
Interview shortcut
> DELETE = some rows. TRUNCATE = all rows, keep the table. DROP = the table is gone.