**Pattern: OR to UNION ALL**

Before (can't use indexes):

SELECT * FROM products

WHERE category = 'Electronics' OR brand = 'Apple';

-- Full table scan: 5M rows, 8 seconds

After (each branch uses index):

SELECT * FROM products WHERE category = 'Electronics'

UNION ALL

SELECT * FROM products WHERE brand = 'Apple' AND category != 'Electronics';

-- Index on category: 500K rows, 0.5s

-- Index on brand: 200K rows, 0.2s

-- Total: 0.7 seconds (11x faster)

Key: Add condition to second query to avoid duplicates

**Pattern: Subquery to JOIN**

Before (correlated subquery):

SELECT

p.product_name,

(SELECT AVG(rating) FROM reviews WHERE product_id = p.id) as avg_rating

FROM products p;

-- Subquery runs 100K times: 45 seconds

After (JOIN with GROUP BY):

SELECT

p.product_name,

AVG(r.rating) as avg_rating

FROM products p

LEFT JOIN reviews r ON p.id = r.product_id

GROUP BY p.id, p.product_name;

-- Single join: 2 seconds (22x faster)

**Pattern: NOT IN to LEFT JOIN**

Before (dangerous with NULLs):

SELECT * FROM customers

WHERE id NOT IN (SELECT customer_id FROM orders);

-- Returns 0 rows if any order has NULL customer_id

-- Also slow: 30 seconds

After (NULL-safe and fast):

SELECT c.* FROM customers c

LEFT JOIN orders o ON c.id = o.customer_id

WHERE o.customer_id IS NULL;

-- Correct results with NULLs: 2 seconds (15x faster)

**Pattern: IN to EXISTS**