How do you rewrite a correlated scalar subquery in the SELECT list as a join or a window function?

## Why rewrite at all A correlated scalar subquery in the SELECT list reads well for one derived value and gets unwieldy for several: each extra metric is another nested block, and the reader has to check each one's correlation separately. Joins and window aggregates state the grouping once. The rewrites are not mechanical, though — each changes something semantically, and knowing what is the point of the question. ## Case 1: aggregating another table → LEFT JOIN + GROUP BY ```sql -- correlated form SELECT c.customer_id, c.name, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS order_count FROM customers c; -- join form SELECT c.customer_id, c.name, COUNT(o.order_id) AS order_count FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id GROUP BY c.customer_id, c.name; ``` Three things must line up: 1. **`LEFT`, not inner.** The correlated form keeps every customer; an inner join silently drops customers with no orders. This is the most common regression when someone "optimises" the subquery away. 2. **`COUNT(o.order_id)`, not `COUNT(*)`.** For a customer with no orders the outer join produces one NULL-extended row. `COUNT(*)` counts rows and reports 1; `COUNT(o.order_id)` counts non-NULL values and reports 0, matching the subquery. Use a column that is never NULL in real matches — the joined table's key. 3. **Every non-aggregated output column in `GROUP BY`.** The subquery form had no grouping to declare; the join form does. ## The multi-subquery trap Two correlated subqueries over two different child tables are independent of each other: ```sql SELECT c.customer_id, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS orders, (SELECT COUNT(*) FROM payments p WHERE p.customer_id = c.customer_id) AS payments FROM customers c; ``` Turning both into joins in one query does **not** work: a customer with 3 orders and 4 payments produces 12 combined rows, and each count is inflated by the other side's cardinality. The correct join-shaped version pre-aggregates each child in its own derived table and joins the results, or counts distinct keys. When someone offers a two-join rewrite of a two-subquery query without addressing this, it is the defect to point at. ## Case 2: aggregating the same table → window aggregate When the subquery reads the *same* table as the outer query and correlates on a grouping key, the natural rewrite is a window aggregate: ```sql -- correlated self-reference SELECT e.emp_id, e.salary, (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.dept_id = e.dept_id) AS dept_avg FROM employees e; -- window form SELECT e.emp_id, e.salary, AVG(e.salary) OVER (PARTITION BY e.dept_id) AS dept_avg FROM employees e; ``` One pass, no second copy of the table, no `GROUP BY` — and rows are preserved, which is exactly what the subquery did. **The equivalence has a condition.** Window functions are evaluated after `WHERE`, over the rows the query has already kept. The subquery version opens an independent scan of `employees` and is unaffected by the outer filter. So with `WHERE e.status = 'ACTIVE'` added, the window form averages active employees only, while the correlated form averages the whole department including inactive staff. Neither is "right" — you must decide which population the metric is supposed to describe, and say so when you present the rewrite. ## What else changes - **Runtime errors disappear.** A scalar subquery raises "more than one row" if the correlation is not unique enough; the join form has no such contract and instead multiplies rows, which is quieter and sometimes worse. - **NULL vs zero.** `COUNT` gives 0 in both forms once you pick the right column; `SUM`/`MAX` give NULL for an empty group either way, so wrap in `COALESCE` if the consumer needs a number. - **Row identity.** If the outer table has duplicate keys, the join form fans out before grouping and can change `SUM`/`AVG` even when `COUNT` looks fine. ## Choosing between them One derived value from a child table, and the query is otherwise simple: leave the subquery, it reads well. Several values from the same child table, or the aggregate feeds further logic: join once and group. Aggregate over the same rows you are already selecting: window function, every time — it is the shortest correct form and needs no second reference to the table.