"What is the difference between DELETE, TRUNCATE and DROP?" is one of the most asked SQL interview questions. The short answer: they remove different things, at different speeds, with different safety nets.
DELETE: remove some or all rows
DELETE FROM leads WHERE status = 'Junk';
DELETE FROM leads; -- all rows- Removes rows; the table and its structure stay.
- Can use WHERE to remove only some rows.
- Works row by row, so it is slower on large tables.
- It is a DML (data manipulation) command and can be rolled back inside a transaction.
- Triggers defined on delete will fire.
TRUNCATE: remove all rows quickly
TRUNCATE TABLE staging_leads;- Removes all rows; the table structure stays.
- No WHERE clause.
- Much faster than DELETE on large tables, because it does not process rows one by one.
- Often resets identity / auto-increment counters.
- Whether it can be rolled back depends on the database (PostgreSQL allows it inside a transaction; some others treat it as auto-committed).
Typical use: clearing a staging table before reloading it.
DROP: remove the whole table
DROP TABLE old_leads;- Removes the table itself: structure, data, indexes and constraints.
- Anything depending on it, such as views, may break or need
CASCADE. - Rolling back depends on the database; in practice, treat it as permanent.
Side by side
| DELETE | TRUNCATE | DROP | |
|---|---|---|---|
| Removes | Selected or all rows | All rows | The whole table |
| WHERE allowed | Yes | No | No |
| Table remains | Yes | Yes | No |
| Speed on big tables | Slower | Fast | Fast |
| Command type | DML | DDL | DDL |
| Fires delete triggers | Yes | Usually no | No |
Safety habits
- Run a SELECT first with the same WHERE clause to see exactly what will be removed.
- Use a transaction where possible:
BEGIN; DELETE ...; SELECT ...;thenCOMMITorROLLBACK. - Never run DELETE without WHERE unless you really mean every row.
- Back up before large deletes or drops on important tables.
- In many companies, analysts have read-only access to production data for exactly this reason.
Related: UPDATE
UPDATE leads SET status = 'Contacted' WHERE lead_id = 1042;The same rule applies: check the WHERE clause with a SELECT before running it.
Practise safely
Our INSERT, UPDATE and DELETE and ALTER and DROP sets run in your browser on practice data, so you can see the effect of each command without risk.
Practise what you just read
Reading helps; writing the code yourself is what sticks. Start here: