CSE Roadmap

Core tier · about 3 hours · 5 parts

Part 3 of 5 · 1 min read

Correlated subqueries

A subquery that references the outer query's current row, re-run once per row.

Think of checking each customer one at a time. For each one, ask whether that specific customer has placed more than one order, instead of computing one shared number for everyone at once.

In plain words: a correlated subquery references a column from the outer query, meaning it cannot run independently. Conceptually, it runs once per row of the outer query, using that row's values each time. This makes a correlated subquery more expressive than an uncorrelated one, and often slower if written carelessly.

A worked example. Finding every customer who has placed more than one order, using a correlated subquery that counts orders for each specific customer row.

The key lines.

SELECT c.full_name
FROM customers c
WHERE (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) > 1;

Correlated subquery

A correlated subquery references a column from its enclosing query, so its result depends on the outer row currently being evaluated, rather than being computed once overall.

Example: o.customer_id = c.customer_id inside the subquery above ties it to whichever customer row the outer query is currently checking.