Correlated Subquery
Subquery that references a column from the outer query, so it's re-evaluated (logically) per outer row. Powerful for per-row comparisons against the row's own group ("above my group's average", "my latest order"). Often rewritable as a window function, usually with a better plan.
Syntax #
SELECT a.id, a.key_col, a.metric
FROM table_name AS a
WHERE a.metric > (
SELECT AVG(x.metric)
FROM table_name AS x
WHERE x.key_col = a.key_col -- correlation
);Example #
Correlated subqueries are easy to reach for and hard to scale. Conceptually one subquery per outer row — modern planners often unroll them into joins, but not always, and not on every engine. Before shipping a correlated subquery on a large table, check the plan (EXPLAIN). For "compare each row to its group's aggregate" the window-function form is almost always faster: one pass, partitioned, no row-by-row evaluation. For "pick one related row per outer row" (latest, max-by-X), a LATERAL join or QUALIFY ROW_NUMBER() = 1 is usually the better tool.