Unit 06.02: Correlated subqueries and their real cost

Unit ID: SQL-M06-U03 - Estimated active time: 13-16 minutes Objective: distinguish a correlated subquery from an independent one, and know when to rewrite it.

The difference is whether it references the outer row

Independent - evaluated once, the same value for every row:

SELECT COUNT(*)
FROM orders o
WHERE o.order_total > (SELECT AVG(order_total) FROM orders);
-- 497

497 orders are above the overall average of ₹2,701.46. The subquery runs once.

Correlated - references the outer query, so conceptually it runs per outer row:

SELECT COUNT(*)
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);
-- 1000

o.customer_id = c.customer_id ties the inner query to the current outer row. That dependency is the definition, and it is also the cost.

When the cost matters

Engines optimise many correlated subqueries into joins, so the naive "one query per row" model is pessimistic. But it is the right mental model for spotting risk: a correlated subquery over a large outer table, with an unindexed correlation column, is where queries go from seconds to minutes.

The rewrite is usually a pre-aggregated join - the same pattern that fixed fan-out in Module 5:

-- Correlated
SELECT c.customer_id,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS orders
FROM customers c;

-- Pre-aggregated join, one pass over each table
SELECT c.customer_id, COALESCE(o.orders, 0) AS orders
FROM customers c
LEFT JOIN (
  SELECT customer_id, COUNT(*) AS orders FROM orders GROUP BY customer_id
) o ON o.customer_id = c.customer_id;

Both return 4,812 rows. The second scales predictably.

EXISTS versus IN versus JOIN

EXISTS - "is there at least one match?" Stops at the first hit. Safe with NULLs.
IN - "is this value in that list?" Readable for small lists.
JOIN - use when you need columns from the other table, not just a yes/no.

Using a join purely to test existence risks fan-out: if the right side has several matches, the left row is duplicated. EXISTS cannot do that, which is why it is the safer existence test.

Practice

Rewrite this as a join and say what could go wrong if you used an inner join instead of the pre-aggregated form.

SELECT c.customer_id
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

Check your answer

SELECT DISTINCT c.customer_id
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;
-- 1000

A plain inner join returns one row per matching order, so a customer with three orders appears three times - you would need DISTINCT to repair it. EXISTS never had that problem, because it asks a yes/no question instead of producing rows.

Takeaway

Correlated means "depends on the outer row". Use EXISTS to test existence, a join to fetch columns, and pre-aggregate when the correlated version starts costing real time.

---