Correlated Subquery vs JOIN - Customer Order Stats

advanced

Calculate order statistics for each customer. Shows the massive performance difference between correlated subqueries and JOINs.

-- Scenario: 1M customers, 10M orders

-- Query: Get customer name with order count and total spent

-- SLOW: Correlated subqueries in SELECT (NEVER do this)

SELECT

c.customer_id,

c.customer_name,

(SELECT COUNT(*) FROM orders WHERE customer_id = c.customer_id) as order_count,

(SELECT SUM(total_amount) FROM orders WHERE customer_id = c.customer_id) as total_spent,

(SELECT MAX(order_date) FROM orders WHERE customer_id = c.customer_id) as last_order_date

FROM customers c;

-- Problem: 3 subqueries × 1M customers = 3M subquery executions

-- Each subquery scans 10M orders

-- Time: 180 seconds (3 minutes!)

-- FAST: Single JOIN with aggregation

SELECT

c.customer_id,

c.customer_name,

COUNT(o.order_id) as order_count,

SUM(o.total_amount) as total_spent,

MAX(o.order_date) as last_order_date

FROM customers c

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

GROUP BY c.customer_id, c.customer_name;

-- Single join operation

-- Time: 2 seconds (90x faster!)

-- OPTIMAL: Add index for even better performance

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- Time: 0.8 seconds (225x faster than correlated!)

**Performance Comparison:**

| Approach | Subquery Executions | Time | Speedup |

|----------|---------------------|------|----------|

| Correlated subqueries | 3M | 180s | 1x |

| JOIN without index | 1 | 8s | 22x |

| JOIN with index | 1 | 2s | 90x |

| JOIN with covering index | 1 | 0.8s | 225x |

**Why So Slow?**

- Correlated: 3 subqueries × 1M customers = 3M executions