DELETE removes rows from a table, one row at a time, optionally filtered by a WHERE clause.
Syntax
DELETE FROM table_name
WHERE condition;Example
DELETE FROM employees
WHERE department = 'Marketing' AND status = 'resigned';Deleting Every Row (Still Keeps the Table)
DELETE FROM employees;This removes all rows but — unlike TRUNCATE — it's logged row-by-row, fires any DELETE triggers, and is fully transactional (can be rolled back before commit).
DELETE vs TRUNCATE vs DROP — Quick Recap
| DELETE | TRUNCATE | DROP | |
|---|---|---|---|
| Removes | Selected rows (or all) | All rows | Table + data + structure |
| WHERE allowed? | Yes | No | No |
| Rollback-friendly? | Yes | Mostly, varies by database | No (DDL) |
| Speed at scale | Slower (row-by-row) | Fast | Fast |
Practical Use Case
Removing specific outdated or invalid records — cancelled orders, expired sessions, duplicate signups — where you need precise control over which rows go.
Common Mistakes
- Forgetting WHERE — same risk as
UPDATE:DELETE FROM employees;with no condition wipes the whole table - Deleting a parent row that's still referenced by a foreign key elsewhere — the database will usually block this unless cascading delete is configured
- Reaching for
DELETEwhen you actually want to clear an entire table fast —TRUNCATEis the better tool there
Interview Relevance
Almost always paired with the TRUNCATE/DROP comparison question. Also expect: "how do you delete duplicate rows, keeping only one copy?" — a classic subquery/window-function problem covered in ROW_NUMBER.
Practice Question
Write a statement that deletes all orders with a status of 'cancelled' that were placed more than a year ago.