SQL DELETE Statement
1. Overview
The DELETE statement removes rows from a table based on specified conditions. Without a WHERE clause, it deletes all rows. Use it carefully to avoid unintended data loss.
2. Basic Syntax
DELETE FROM table_name
WHERE condition;
Always include WHERE to target specific rows unless you intentionally want to clear the table.
3. Sample Data Before Deletion
| id | first_name | department |
|---|---|---|
| 1 | Alice | Engineering |
| 2 | Bob | Marketing |
| 3 | Cathy | HR |
| 4 | Dan | Sales |
4. Simple DELETE
DELETE FROM employees
WHERE department = 'HR';
Explanation: Removes all employees in the HR department.
After Deletion
| id | first_name | department |
|---|---|---|
| 1 | Alice | Engineering |
| 2 | Bob | Marketing |
| 4 | Dan | Sales |
5. Conditional Bulk DELETE
DELETE FROM employees
WHERE id IN (2, 4);
Explanation: Deletes employees with id 2 and 4.
After Deletion
| id | first_name |
|---|---|
| 1 | Alice |
6. DELETE with Subquery
DELETE FROM employees
WHERE department IN (
SELECT name FROM departments WHERE location = 'Remote'
);
Explanation: Deletes employees whose department is located remotely, based on a subquery.
7. Truncate vs. Delete
TRUNCATE TABLE employees; removes all rows quickly by deallocating data pages, resetting identity counters, and cannot be rolled back in some DBs. DELETE logs each row removal and can be transactional.
After TRUNCATE
| (no rows) |
|---|
8. Cascading Deletes
With foreign keys defined ON DELETE CASCADE, deleting parent rows automatically removes related child rows.
-- Parent: departments(id)
-- Child: employees(dept_id) ON DELETE CASCADE
DELETE FROM departments WHERE id = 10; -- cascades to employees
9. Performance Tips
Index columns used in WHERE for efficient row identification.Batch large deletes with limits (e.g., DELETE ... LIMIT 1000) to avoid long locks.
Disable triggers/indexes during bulk trims, re-enable afterward.
Use EXPLAIN to verify delete plan and adjust predicates.
10. Summary
The DELETE statement is a powerful tool for data cleanup and maintenance. Combine it with subqueries, cascading rules, and careful batching to manage data safely and effectively.