Query
Correlated subquery
Subquery that references columns from the outer query, so it is logically re-evaluated for every outer row.
A correlated subquery contains a reference to a column from the enclosing query, so its result depends on the current outer row:
SELECT u.id,
(SELECT count(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;
Conceptually the inner query runs once per outer row. In practice the planner may rewrite it into a join, a hash subplan, or a Memoize-cached Nested Loop, depending on cardinality estimates. EXPLAIN will reveal which strategy was chosen.
Correlated subqueries are easy to write and easy to make slow. For "row plus aggregate" patterns prefer a LEFT JOIN ... GROUP BY; for "row plus top-N" patterns prefer a LATERAL join. Reach for a correlated subquery when the alternative is significantly less readable.