What does LEFT JOIN LATERAL ... ON TRUE give you that JOIN LATERAL does not?
answer
- a LATERAL join is still a join
- empty subquery result decides the row's fate
- outer join syntax demands a join specification
- the correlation already lives inside the subquery
- NULL-extended columns instead of a dropped row
basics
~20 sIt keeps left-hand rows whose LATERAL subquery returned no rows, NULL-extending the subquery's columns instead of dropping the row. The inner forms, CROSS JOIN LATERAL and JOIN LATERAL ... ON TRUE, discard those rows entirely.
solid answer
~50 sA LATERAL join is still a join, so the usual inner/outer rules apply. With `CROSS JOIN LATERAL` — or the equivalent `JOIN LATERAL ... ON TRUE` — a left row whose subquery yields zero rows produces no output at all: customers with no orders vanish from the report. `LEFT JOIN LATERAL (...) AS t ON TRUE` preserves every left row and fills `t`'s columns with NULLs when the subquery came back empty. The `ON TRUE` looks odd but is required: outer-join syntax demands an `ON` or `USING` clause, and all the correlation work already happened inside the subquery, so there is no join predicate left to write. On engines without a boolean literal, `ON 1 = 1` is the portable spelling. SQL Server expresses the same thing as `OUTER APPLY`, which needs no predicate at all.
code
sql · 17 lines-- Inner: customers with no orders vanish
SELECT c.name, last_order.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 last_order;
-- Outer: every customer appears, total is NULL when there is no order
SELECT c.name, last_order.total
FROM customers c
LEFT 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 last_order ON TRUE;go deeper
Remember that the inner form drops left rows whose subquery found nothing, and that the outer form keeps them with NULLs in the subquery's columns.
Explain why the ON clause is syntactically required and why TRUE is what goes in it, and be able to write both forms from memory against a customers-and-orders schema.
Show the judgment: decide from what a result row is supposed to mean which form is correct, and recognise the outer WHERE predicate that quietly collapses the outer form back to inner.
Set the convention — which spelling the team uses, how reviewers check that optional matches are intentional, and how the pattern maps onto the APPLY spelling on engines that use it.
## LATERAL joins obey the ordinary join rules Adding `LATERAL` changes *scoping* — the subquery may reference columns of the items to its left — but it does not change what kind of join you wrote. `CROSS JOIN LATERAL` and `JOIN LATERAL ... ON TRUE` are inner: an output row exists only where the right side produced a row. If the subquery returns zero rows for a given left row, that left row is not in the result. That is the single most common LATERAL bug. This query silently reports only customers who have ordered at least once: ```sql SELECT c.name, last_order.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 last_order; ``` A brand-new customer with no orders disappears — no row, not even a row of NULLs. Nothing in the query looks like a filter, which is exactly why the bug survives review. ## The outer form ```sql SELECT c.name, last_order.total FROM customers c LEFT 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 last_order ON TRUE; ``` Now every customer appears exactly once. For customers with orders, `last_order.total` holds the newest order's total; for customers without, it is NULL, just as with any ordinary `LEFT JOIN` against an unmatched right side. Wrap it in `COALESCE(last_order.total, 0)` if you want a zero rather than a NULL in the report. ## Why ON TRUE Outer-join syntax requires a join specification: `LEFT JOIN <table> ON <predicate>` or `LEFT JOIN <table> USING (...)`. With a LATERAL derived table there is nothing sensible to put there, because the matching condition (`o.customer_id = c.id`) already lives *inside* the subquery — that is the whole point of the construct. `ON TRUE` is the conventional way to say "match whatever the subquery gave me for this row". It is not a no-op you could delete: omit it and the statement is a syntax error. A few notes on spelling. `ON TRUE` needs a boolean literal, which some engines' SQL dialects do not have; `ON 1 = 1` means the same thing and is the safer spelling in portable code. For the inner case, `CROSS JOIN LATERAL (...)` and `JOIN LATERAL (...) ON TRUE` are equivalent, and the `CROSS` form is preferred precisely because it does not invite a reader to look for a meaningful predicate. SQL Server offers `CROSS APPLY` and `OUTER APPLY`, neither of which takes a predicate at all. ## Do not put the predicate in ON A tempting mistake is to move the correlation out of the subquery and into the `ON` clause: ```sql LEFT JOIN LATERAL (SELECT o.total, o.customer_id FROM orders o ORDER BY o.placed_at DESC FETCH FIRST 1 ROW ONLY) AS last_order ON last_order.customer_id = c.id ``` This is legal but means something completely different: the subquery now picks the single newest order **in the whole table**, and the `ON` predicate merely checks whether it happens to belong to this customer. Almost every customer gets NULLs. With LATERAL, the per-row condition belongs inside the subquery, where it can influence which rows the `ORDER BY` and the row limit see. ## The mirror-image trap The outer form is also easy to undo. Adding `WHERE last_order.total > 100` after a `LEFT JOIN LATERAL` throws away every NULL-extended row and collapses the result back to inner-join shape, because the NULL comparison is not true. If the predicate is meant to select among the subquery's rows, put it inside the subquery; if it is meant to filter the final result, be sure you actually want the unmatched rows gone. ## Choosing between them Ask what the row means. A per-customer dashboard row should exist for every customer, so use the outer form and let the columns be NULL. A feed of "latest order per customer" only makes sense where an order exists, so the inner form is correct and the disappearance of order-less customers is the intended behaviour. State that intent in review; "which rows survive when the subquery is empty?" is the question an interviewer is really asking.
- Is CROSS JOIN LATERAL the same as JOIN LATERAL ... ON TRUE?Yes — both are inner, so a left row survives only if the subquery returned at least one row. The CROSS form is usually preferred because it does not display an ON clause that a reader might mistake for a meaningful matching condition.
- You wrote LEFT JOIN LATERAL but order-less customers still disappear. What is the likely cause?A predicate on the right-hand columns in the outer WHERE clause. `WHERE last_order.total > 100` is not true for a NULL-extended row, so it discards exactly the rows the outer join preserved. Move the condition inside the subquery, or allow the NULLs explicitly.
- Can you write ON 1 = 1 instead of ON TRUE?Yes, and it is the safer spelling for portable code, because not every engine's SQL dialect has a boolean literal. Both say the same thing: there is no additional matching condition to apply, since the correlation is inside the subquery.
saying these in an interview costs you the question
- Thinking LATERAL always preserves every left-hand row
- Believing ON TRUE is optional decoration you can delete
- Moving the correlation predicate into ON and expecting the same result
- Filtering right-side columns in WHERE and still expecting unmatched rows
- Assuming an empty subquery yields a row of zeros rather than no row