DELETE

Definition

DELETE removes existing rows from a table, without changing the table's structure. Its WHERE clause is what makes it targeted instead of total — this is the same statement discussed at a high level in Chapter 9's DDL comparison (DROP vs TRUNCATE vs DELETE); this topic focuses on DELETE's own mechanics in depth.

How It Works

sql
DELETE FROM employees WHERE salary < 60000;

This removes every employee earning less than 60,000, leaving the rest of the table untouched — the table itself, its columns, indexes, and constraints all remain exactly as they were. DELETE is fully transactional: it can be wrapped in a transaction and rolled back before commit (11.11), and it fires any DELETE triggers (Chapter 30) row by row.

Edge Cases and Pitfalls

• DELETE FROM employees; with no WHERE clause removes EVERY row — this is a genuinely common, genuinely damaging real-world mistake, and the exact reason SELECT-first previewing is standard practice.
• DELETE respects foreign key constraints: deleting a row that's still referenced by a child table (with no cascading action configured) is rejected, not silently allowed to create orphaned references (see 5.11, 5.12).
• For removing ALL rows from a table (no filtering needed), TRUNCATE TABLE (9.6) is typically much faster than DELETE FROM table with no WHERE, since TRUNCATE deallocates the whole data structure at once rather than removing rows one-by-one with full transaction logging — but TRUNCATE can't be filtered and, on some engines, is less safely rollback-able.

Key Takeaways

• DELETE FROM table WHERE condition removes matching rows; the table structure is untouched.
• DELETE respects foreign key constraints and is fully transactional/rollback-able.
• For unconditionally emptying a whole table, TRUNCATE is typically faster than a WHERE-less DELETE, at the cost of losing per-row filtering and some rollback guarantees.