SQL — Delete

Basic DELETE

DELETE FROM users
WHERE id = 1;

DELETE with conditions

DELETE FROM users
WHERE status = 'inactive';

DELETE with subquery

DELETE FROM users
WHERE id IN (
  SELECT user_id FROM orders WHERE total = 0
);

DELETE with JOIN

-- MySQL
DELETE u FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.total < 10;

-- PostgreSQL
DELETE FROM users
USING orders
WHERE users.id = orders.user_id
AND orders.total < 10;

TRUNCATE vs DELETE

-- DELETE: removes rows, logs each row, can be rolled back
DELETE FROM users;

-- TRUNCATE: removes all rows, faster, minimal logging
TRUNCATE TABLE users;

DELETE returning

-- PostgreSQL
DELETE FROM users
WHERE status = 'inactive'
RETURNING id, name;

Best practices

• Always use WHERE clause with DELETE
• Back up data before mass deletes
• Use LIMIT for large deletions
• Test with SELECT first

Mini Practice

Write SQL code that:

1. Deletes a specific row
2. Deletes with a condition
3. Uses a subquery in DELETE
4. Compares DELETE and TRUNCATE

Up Next

In the next lesson, you'll learn about Order By — sorting query results.