What does the LATERAL keyword let a derived table in FROM do?
answer
- FROM items normally cannot see each other
- one keyword lifts that scoping restriction
- visibility runs left to right only
- evaluated once per row of the left input
- SQL Server spells it CROSS APPLY
basics
~20 sLATERAL lets a derived table in the FROM clause reference columns of the FROM items written before it. Semantically the subquery is evaluated once per left-hand row, so a correlated subquery becomes a join that can return many rows and many columns.
solid answer
~40 sNormally every item in a FROM clause is resolved independently: a derived table cannot see `c.id` from a table joined earlier, and the only place the two sides meet is the `ON` predicate. `LATERAL` removes that restriction for the item it marks — inside `CROSS JOIN LATERAL (SELECT ... WHERE o.customer_id = c.id) t`, the columns of `c` are in scope. The defined meaning is per-row: for each row of the left input, substitute its values, evaluate the subquery, and emit one output row per row it returned. Because it is a join and not a scalar subquery, the right side may return several columns and several rows, and may carry its own `ORDER BY` and row limit. Visibility is left-to-right only, and the derived table still needs an alias.
go deeper
Know that a derived table in FROM normally cannot reference another table in the same FROM clause, and that LATERAL is the keyword that lifts that restriction.
Be ready to explain the per-row evaluation model, where the keyword sits syntactically, that the alias is still mandatory, and that only items to the left of it are visible.
Show where you actually reach for it — per-row top-N and per-row lookups returning several columns — and that the inner form silently drops left rows whose subquery is empty.
Own the portability call: the construct is standard but SQL Server exposes it as APPLY and some engines lack it, so decide whether it belongs in shared, multi-engine query code.
## The default: FROM items are independent In an ordinary `FROM` clause each item — a base table, a view, a parenthesized derived table — is resolved on its own. Names introduced by one item are **not** in scope inside another item; the only place the two sides are allowed to meet is the join predicate in `ON`. That is why this fails: ```sql SELECT c.name, recent.total FROM customers c JOIN (SELECT o.total FROM orders o WHERE o.customer_id = c.id) AS recent ON TRUE; -- invalid ``` The correlation name `c` simply does not exist inside the derived table. Engines report it differently ("invalid reference to FROM-clause entry for table c", "Unknown column 'c.id' in 'where clause'"), but the rule is the same everywhere: a plain derived table is uncorrelated. ## What LATERAL changes `LATERAL` marks one `FROM` item as *dependent* on the items to its left. Inside a LATERAL derived table you may reference any correlation name that appears earlier in the same `FROM` clause: ```sql SELECT c.name, recent.total FROM customers c CROSS JOIN LATERAL (SELECT o.total FROM orders o WHERE o.customer_id = c.id ORDER BY o.placed_at DESC FETCH FIRST 1 ROW ONLY) AS recent; ``` Visibility is strictly left-to-right: a LATERAL item can see what precedes it, never what follows it. Reordering the `FROM` clause therefore changes what compiles, which is unusual in SQL and worth saying out loud in an interview. ## The per-row mental model The semantics are defined per row of the left input: take a row of `customers`, substitute its column values into the subquery, evaluate the subquery, and emit one output row for every row the subquery produced, with the left row's columns attached. Ten customers with three matching orders each yield thirty rows. This is the *meaning* of the construct; how an engine actually executes it is a separate matter and not something the language dictates. ## Syntax and shape `LATERAL` goes directly before the parenthesized subquery: `CROSS JOIN LATERAL (...) AS t`, `JOIN LATERAL (...) AS t ON TRUE`, `LEFT JOIN LATERAL (...) AS t ON TRUE`, or in the comma form `FROM customers c, LATERAL (...) AS t`. The alias is mandatory, exactly as for any derived table, and you can name the output columns too: `AS t(total, placed_at)`. ## What you gain over a correlated scalar subquery A correlated subquery in the `SELECT` list must return **at most one row and exactly one column**; more than one row is a runtime error. A LATERAL derived table has neither restriction. One per-row subquery can hand back several columns at once (`recent.total`, `recent.placed_at`, `recent.status`), or several rows (the three latest orders), and its result can be filtered, grouped or joined onward like any other table. It can also carry `ORDER BY` plus `FETCH FIRST n ROWS ONLY`, which is what makes it the natural way to express "the newest N rows per group". ## Inner by default `CROSS JOIN LATERAL` and `JOIN LATERAL ... ON TRUE` have inner-join semantics: if the subquery returns zero rows for a left row, that left row disappears from the result. To keep it, write `LEFT JOIN LATERAL (...) AS t ON TRUE`, which NULL-extends the right-hand columns instead of dropping the row. Forgetting this is the classic LATERAL bug — a report that silently loses every customer with no orders. ## Portability `LATERAL` is part of the ISO SQL standard, but the spelling is not universal. SQL Server expresses the same idea as `CROSS APPLY` and `OUTER APPLY` (`FROM customers c CROSS APPLY (SELECT ...) recent`), which map onto `CROSS JOIN LATERAL` and `LEFT JOIN LATERAL ... ON TRUE` respectively. PostgreSQL supports `LATERAL`; MySQL added it in 8.0.14; other engines vary, so check the documentation before you standardise on it. Mentioning the `APPLY` equivalence is a cheap way to show you have met the construct on more than one engine. ## When to reach for it Use LATERAL when the right-hand computation genuinely depends on the current left row and produces more than a single scalar: per-row top-N, per-row lookups that return several columns, per-row expansion of one row into many. If the right side does not reference the left side at all, you do not need LATERAL — a plain derived table joined on a predicate is simpler and says what it means.
- Does the order of items in the FROM clause matter when LATERAL is involved?Yes. A LATERAL item may reference only correlation names introduced to its left; anything listed after it is not in scope. Swapping the two sides of the join can turn a valid query into a name-resolution error, which is unusual for SQL and is exactly why the keyword exists.
- How does a LATERAL derived table differ from a correlated scalar subquery in the SELECT list?A scalar subquery must return at most one row and exactly one column, and returning two rows is a runtime error. A LATERAL derived table can return many rows and many columns, can carry its own ORDER BY and row limit, and its result can be filtered, aggregated or joined onward like any other table.
- Do you need LATERAL if the subquery never mentions the left-hand table?No. An uncorrelated derived table is already legal in FROM; adding LATERAL buys you nothing there. Reserve the keyword for subqueries that genuinely depend on the current left row, so its presence signals per-row evaluation to the next reader.
saying these in an interview costs you the question
- Claiming any derived table can reference a table joined before it
- Describing LATERAL as a performance hint rather than a scoping rule
- Saying CROSS JOIN and CROSS JOIN LATERAL mean the same thing
- Assuming a LATERAL item can reference tables listed after it
- Expecting left rows to survive when the subquery returns no rows