SQL DELETE FROM WHERE — Removing Rows Safely

Deleting Data

The DELETE statement removes rows from a table. Like UPDATE, forgetting the WHERE clause deletes all rows.

Basic Delete

DELETE FROM employees
WHERE id = 42;

Delete with Subquery

DELETE FROM employees
WHERE department_id IN (
  SELECT id FROM departments WHERE is_archived = true
);

Delete with JOIN

PostgreSQL uses USING:

DELETE FROM employees e
USING departments d
WHERE e.department_id = d.id
  AND d.name = 'Legacy'
RETURNING e.id, e.name;

MySQL uses JOIN:

DELETE e FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.name = 'Legacy';

TRUNCATE vs DELETE

Feature	DELETE	TRUNCATE
WHERE clause	Yes	No (all rows)
Triggers fired	Yes	No
Can ROLLBACK	Yes (in transaction)	Depends on DB
Speed	Slower (row-by-row)	Faster (page-level)
Resets auto-increment	No	Yes (MySQL, PostgreSQL)

-- Remove all rows quickly
TRUNCATE TABLE temp_imports;

Cascading Deletes

If the table has foreign keys defined with ON DELETE CASCADE, deleting a parent row automatically deletes related child rows:

-- When departments table has: ON DELETE CASCADE
DELETE FROM departments WHERE id = 5;
-- All employees in department 5 are also deleted

Safety Checklist

1. Run a SELECT with the same WHERE first to verify scope.
2. Wrap in a transaction (BEGIN ... ROLLBACK / COMMIT).
3. Back up the table if deleting large volumes.