DELETE
SQL · Modifying Data
Theory
DELETE removes rows from a table. DELETE FROM employees WHERE city = 'Pune' removes only the employees in Pune; the table and its other rows stay. Without a WHERE clause, every row is deleted, but the empty table remains (unlike DROP TABLE).
DELETE works row by row: each removed row is logged, DELETE triggers fire, and foreign keys are checked, so deleting a customer who still has orders is blocked unless the foreign key uses ON DELETE CASCADE or SET NULL. Because it is ordinary DML, it can be rolled back inside a transaction. On big tables, deleting millions of rows is slow; delete in batches, or use TRUNCATE when every row must go. Many teams prefer a soft delete (an is_active flag) so history is kept.
Why we use it
Data sometimes must go: test records, cancelled sign-ups, rows past their retention period, or personal data a customer asked to remove. DELETE removes exactly the rows you choose and nothing else.
When to use it
Use DELETE with WHERE to remove specific rows. Use TRUNCATE to empty a whole table quickly, DROP TABLE to remove the table itself, and a soft-delete flag when you may need the history later.
How it works
- DELETE FROM employees WHERE ...
- Read the next row
- Does it match the WHERE condition? Yes: Check foreign keys and fire triggers. No: Keep the row.
- Is the delete allowed? Yes: Remove and log the row. No: Error: referenced by child rows.
- Report how many rows were deleted
Syntax
DELETE FROM table
WHERE condition;Example
DELETE FROM employees WHERE name = 'Asha Rao';Key points
- Without WHERE, every row is deleted (the table remains)
- Slower than TRUNCATE for clearing an entire table
- Fires DELETE triggers and checks foreign keys row by row
- Can be rolled back inside a transaction
- Run the WHERE as a SELECT first to see what will be removed