SQL: a correlated subquery runs once per outer row

If the subquery references a column from the outer query it cannot be evaluated once. On a large outer set that is millions of executions, and a join usually does the same work once.

Code

-- correlated: one execution per customer
SELECT c.name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS n
  FROM customer c;

-- aggregated once, then joined
SELECT c.name, COALESCE(o.n, 0) AS n
  FROM customer c
  LEFT JOIN (SELECT customer_id, COUNT(*) n FROM orders GROUP BY customer_id) o
    ON o.customer_id = c.id;

Output

correlated : SubPlan  (loops=9876)   -> 4,180 ms
joined     : Hash Left Join          ->   210 ms

-- Planners increasingly rewrite simple correlated subqueries into joins on
-- their own. EXPLAIN tells you whether yours did - look for "SubPlan" and
-- a high loops= count.

Run this yourself in the Online Java Compiler, spin up a live REST API in the API Sandbox, or practise with Java interview questions.

Published 2026-08-25