Why does one LATERAL join beat three scalar correlated subqueries in a SELECT list?

## The shape people write first A per-row lookup that needs several values usually starts life as a stack of scalar subqueries: ```sql SELECT c.name, (SELECT o.total FROM orders o WHERE o.customer_id = c.id ORDER BY o.placed_at DESC FETCH FIRST 1 ROW ONLY) AS last_total, (SELECT o.status FROM orders o WHERE o.customer_id = c.id ORDER BY o.placed_at DESC FETCH FIRST 1 ROW ONLY) AS last_status, (SELECT o.placed_at FROM orders o WHERE o.customer_id = c.id ORDER BY o.placed_at DESC FETCH FIRST 1 ROW ONLY) AS last_placed_at FROM customers c; ``` It works, and for a while nobody complains. There are three things wrong with it, and only one of them is aesthetics. ## Problem 1: the columns are not guaranteed to agree Each subquery is a separate expression, resolved on its own. `ORDER BY o.placed_at DESC` is not a total ordering when two orders share a timestamp, so each subquery is free to pick a different one of the tied rows. The result is a row of output that mixes `total` from order A with `status` from order B — a record that exists nowhere in the database. This is a genuine correctness defect, not a style preference, and it is invisible until the data contains a tie. One LATERAL join eliminates it by construction, because the row is chosen once: ```sql SELECT c.name, last_order.total, last_order.status, last_order.placed_at FROM customers c LEFT JOIN LATERAL (SELECT o.total, o.status, o.placed_at FROM orders o WHERE o.customer_id = c.id ORDER BY o.placed_at DESC, o.id DESC FETCH FIRST 1 ROW ONLY) AS last_order ON TRUE; ``` Whatever row the subquery selects, all three columns come from it. ## Problem 2: a scalar subquery is boxed in A subquery used as a value expression must yield exactly one column, and at most one row — returning two rows raises a runtime error rather than a wrong answer. That ceiling means the scalar shape can never be extended to "the three latest orders" or "the matching row plus a computed column" without being rewritten. A LATERAL derived table has no such ceiling: change `FETCH FIRST 1 ROW ONLY` to `3` and the same query becomes a top-N-per-group query. The construct grows with the requirement. ## Problem 3: the result cannot be used further Columns produced by scalar subqueries in the `SELECT` list are not visible to `WHERE`, `GROUP BY` or `HAVING` in the same query — select-list aliases are not in scope there — so filtering on `last_total` means either repeating the whole subquery or wrapping the query in another level. The LATERAL version publishes `last_order` as a real `FROM` item, so `WHERE last_order.status = 'PAID'` and `GROUP BY last_order.status` just work, and downstream joins can reference it. That same property gives a neat side use: a LATERAL derived table can name an intermediate expression once and make it usable everywhere in the query. ```sql SELECT o.id, calc.net, calc.net * 0.2 AS tax FROM orders o CROSS JOIN LATERAL (SELECT o.gross - o.discount AS net) AS calc WHERE calc.net > 100; ``` Here `calc.net` is a column, not a select-list alias, so it is legal in the `SELECT` list, the `WHERE` clause and any `GROUP BY`. One caveat: this spelling relies on a `SELECT` with no `FROM` clause, which some engines do not accept — Oracle wants `FROM DUAL`, and where a table value constructor is available, `LATERAL (VALUES (o.gross - o.discount)) AS calc(net)` expresses the same thing. ## What the scalar form still has going for it Honesty matters in the answer. A single scalar subquery for a single value is perfectly idiomatic and reads well — `(SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count` needs no LATERAL. The rewrite earns its keep when the same per-row lookup feeds more than one output column, when the chosen row must be internally consistent, or when the result has to be referenced outside the select list. Reach for the join when you notice the same correlated subquery pasted twice. ## How to say it in an interview Lead with the correctness argument — separate subqueries may disagree about which row they picked — then add the extensibility and composability points. Candidates who only say "it is cleaner" are describing the least important of the three differences.