What Is DELETE?

DELETE FROM customers WHERE id = 5;

Basic Syntax

-- Delete a specific customer
DELETE FROM customers WHERE id = 5;

-- Delete all customers from a city
DELETE FROM customers WHERE city = 'New York';

-- Delete with multiple conditions
DELETE FROM orders
WHERE order_date < '2023-01-01'
AND status = 'Cancelled';

Delete with Subqueries

Subqueries let you delete rows based on data in other tables.

-- Delete customers who have never placed an order
DELETE FROM customers
WHERE customer_id NOT IN (
    SELECT DISTINCT customer_id FROM orders
);

-- Delete old audit logs
DELETE FROM audit_logs
WHERE created_at < (
    SELECT DATE_SUB(MAX(created_at), INTERVAL 90 DAY)
    FROM audit_logs
);

-- Safer: NOT EXISTS avoids NULL pitfalls
DELETE FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Safe Delete Pattern

Follow a three-step process: preview, execute, verify.

-- Step 1: Preview rows to be deleted
SELECT * FROM customers WHERE city = 'Chicago';

-- Step 2: Delete the rows
DELETE FROM customers WHERE city = 'Chicago';

-- Step 3: Verify deletion
SELECT * FROM customers WHERE city = 'Chicago';

BEGIN TRANSACTION;

DELETE FROM orders
WHERE order_date < '2022-01-01'
AND status = 'Archived';

-- Check how many rows were deleted
SELECT ROW_COUNT();

-- COMMIT;   -- if satisfied
-- ROLLBACK; -- if something went wrong

DELETE vs TRUNCATE vs DROP

Understanding when to use each operation prevents data loss and improves performance.

-- Use DELETE when you need row-level control
DELETE FROM employees WHERE status = 'Terminated';

-- Use TRUNCATE when removing ALL rows quickly
TRUNCATE TABLE temp_data;

-- Use DROP when removing the entire table
DROP TABLE old_archive;

Batch Deletes

For large datasets, delete in batches to avoid long locks and transaction log growth.

-- Delete in batches of 1000 rows
DELETE FROM logs
WHERE created_at < '2023-01-01'
LIMIT 1000;

-- Repeat until no rows are affected
-- Check with SELECT COUNT(*) first
SELECT COUNT(*) FROM logs WHERE created_at < '2023-01-01';

Common Pitfalls

-- Dangerous: Deletes ALL rows
DELETE FROM employees;

-- Safe: Targeted delete
DELETE FROM employees WHERE department = 'Temp';

Performance Considerations

1. Index the WHERE columns — speeds up row lookup
2. Batch large deletes — reduces lock duration
3. Check foreign keys — dependent rows may block deletion
4. Monitor transaction logs — DELETE generates significant log entries
5. Use EXPLAIN — verify the query plan before deleting

-- Check the execution plan
EXPLAIN DELETE FROM employees
WHERE department = 'Engineering';

Practice Exercises

Exercise 1: Write a DELETE to remove all products with zero stock.

-- Solution
DELETE FROM products
WHERE stock_quantity = 0;

Exercise 2: Delete inactive customers who have never placed an order.

-- Solution
DELETE FROM customers
WHERE status = 'Inactive'
AND customer_id NOT IN (SELECT DISTINCT customer_id FROM orders);

Exercise 3: Use a transaction to safely delete old order items.

-- Solution
BEGIN TRANSACTION;

DELETE FROM order_items
WHERE order_id IN (
    SELECT order_id FROM orders
    WHERE order_date < '2022-01-01'
);

-- Verify before committing
SELECT COUNT(*) AS remaining
FROM order_items
WHERE order_id IN (
    SELECT order_id FROM orders
    WHERE order_date < '2022-01-01'
);

-- COMMIT; -- if count is 0

Summary