Subquery to JOIN

Before (Inefficient Query)

SELECT p.name, p.price
FROM products p
WHERE p.category_id IN (
    SELECT c.id 
    FROM categories c 
    WHERE c.name = 'Electronics'
);

After (Optimized Query)

-- Use JOIN instead of subquery
SELECT p.name, p.price
FROM products p
INNER JOIN categories c ON p.category_id = c.id
WHERE c.name = 'Electronics';

Why This Optimization Works

1. Correlated Subqueriesi: The original query runs the subquery for each row in the outer query (N+1 problemi). With 10,000 products, this means 10,001 queries!

2. Single Executioni: JOINs execute once, combining tables in a single operation. The database can then use efficient join algorithms.

3. Optimizer Friendlyi: JOINs give the query optimizer more flexibility to choose the best execution plan.

More Subquery Examples

IN vs EXISTS

Before (Inefficient Query)

-- Using IN with subquery
SELECT * FROM products 
WHERE category_id IN (SELECT id FROM categories);

After (Optimized Query)

-- Using EXISTS (stops at first match)
SELECT * FROM products p
WHERE EXISTS (SELECT 1 FROM categories c WHERE c.id = p.category_id);

NOT IN vs NOT EXISTS

Before (Inefficient Query)

-- NOT IN (can return NULL issues)
SELECT * FROM products 
WHERE category_id NOT IN (SELECT id FROM categories WHERE active = true);

After (Optimized Query)

-- Using NOT EXISTS
SELECT * FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM categories c 
    WHERE c.id = p.category_id AND c.active = true
);

Common Anti-Patterns

Anti-Pattern	Better Approach
SELECT * in subquery	SELECT 1 or SELECT id
Nested subqueries	Use CTEs or temp tables
Subquery in SELECT	Use JOIN or window functions
Subquery in WHERE	Use JOIN or EXISTS