When would you standardise on LATERAL for per-row lookups across a multi-engine codebase?
answer
- list every engine the SQL truly reaches
- the same idea has more than one spelling
- some alternatives are portable but less literal
- one construct can prevent a class of bug
- write the rule down and date it
basics
~20 sStandardise on it when every target engine supports it or its APPLY spelling and the queries genuinely need per-row subqueries returning several rows or columns. Otherwise prefer formulations that are portable everywhere and keep the LATERAL variants isolated behind engine-specific code.
solid answer
~50 sTreat it as a portability decision, not a taste decision. `LATERAL` is standard SQL and widely available — PostgreSQL supports it, MySQL added it in 8.0.14, and SQL Server expresses the same idea as `CROSS APPLY` / `OUTER APPLY` — but the spelling differs and some engines have neither, so the first question is what the deployment matrix actually contains. If one engine dominates and the others are only dev conveniences, adopt it and abstract the exceptions. If queries must run unchanged everywhere, restrict it to the cases where nothing else expresses the requirement as clearly — per-row top-N, per-row lookups yielding several consistent columns, per-row expansion — and use ranking-window or aggregate-then-join formulations elsewhere. Then make the decision reviewable: a written rule, an example of each accepted form, and a note that the inner form drops rows whose subquery is empty. Remember the semantics are per left row, so the pattern grows with the size of the driving side.
go deeper
You are unlikely to be asked this, but know that the construct is standard SQL and that some engines spell it CROSS APPLY or OUTER APPLY instead.
Be able to say what breaks if a query using it is pointed at an engine that lacks it, and which alternative formulations express per-group top-N portably.
Argue from the deployment matrix and from bug classes: which uses prevent a real defect, which are only tidier, and how the inner form's row-dropping is caught in review.
Own the decision end to end — enumerate the engines, weigh clarity against portability, isolate engine-specific spellings behind one boundary, write the rule down, and set a date to revisit it.
## What the decision really is The question is not "is LATERAL good". It is: should a construct whose spelling differs between engines become part of the vocabulary every engineer on the team is expected to read and write? That is a governance call with three inputs — where the SQL runs, what the alternatives cost in clarity, and who has to maintain it. ## Input 1: the deployment matrix Start by writing down every engine the SQL actually reaches, including the ones nobody counts: the production database, the analytics warehouse, the local development database, the embedded engine used in tests, and any BI tool that rewrites queries. `LATERAL` is part of the ISO SQL standard. PostgreSQL supports it; MySQL added support in 8.0.14; SQL Server offers the equivalent as `CROSS APPLY` and `OUTER APPLY`, which map onto `CROSS JOIN LATERAL` and `LEFT JOIN LATERAL ... ON TRUE` respectively. Other engines vary and some have neither, so verify each entry in your matrix against its own documentation rather than assuming. A single environment that lacks the construct — often a lightweight test database — is enough to make "write it once, run it everywhere" false, and that is usually discovered late, when the test suite cannot run a query production depends on. There is also a small dialect detail worth pinning in the standard: `LEFT JOIN LATERAL` requires a join specification, and `ON TRUE` needs a boolean literal that not every dialect has. `ON 1 = 1` says the same thing and travels further. ## Input 2: what you give up by avoiding it Banning the construct is not free. Several requirements have no equally direct expression: - **Per-row top-N.** The alternative formulations exist and are widely portable, but they state the requirement less literally than "sort this group's rows and take three". - **A per-row lookup feeding several columns.** Without LATERAL this becomes repeated scalar subqueries, which can disagree with each other when the ordering is not total — a correctness cost, not just a verbosity cost. - **Per-row expansion**, where one left row produces a variable number of right rows computed from its values. Weigh those against the cost of a second dialect in the codebase. Where the alternative is merely more verbose, portability usually wins; where the alternative is genuinely more error-prone, that is an argument for adopting the construct and paying the abstraction cost. ## Input 3: the semantics your team must internalise Anyone writing it needs three facts in their head: visibility is left-to-right only; evaluation is defined per row of the left input, so the work scales with the driving side's cardinality; and the inner form silently drops left rows whose subquery returned nothing. The third is the one that produces production incidents — a dashboard that quietly omits every customer with no orders. If the team cannot reliably recall that, the constraint is training, not syntax. ## Turning the call into a rule Whatever you decide, make it checkable: 1. **Name the accepted forms.** For example: `CROSS JOIN LATERAL` for mandatory matches, `LEFT JOIN LATERAL ... ON TRUE` for optional ones, and no comma form. 2. **Say where it is allowed.** Perhaps free in the engine-specific reporting layer, forbidden in queries shipped to every environment. 3. **Isolate the exceptions.** If one engine needs `CROSS APPLY`, keep the two spellings side by side in one place rather than scattering conditionals through the codebase. 4. **Give reviewers a checklist.** Is the left side one row per group? Does the subquery's `ORDER BY` include a unique tiebreaker? Is the inner-versus-outer choice deliberate? 5. **Revisit on upgrade.** A rule written when the fleet ran an older engine may be obsolete a year later; date the decision and re-check it. ## The answer an interviewer is listening for A weak answer picks a side on aesthetics. A strong one enumerates the engines, distinguishes cases where the construct prevents a class of bug from cases where it merely reads nicely, admits the operational risk of the inner form, and ends with a written rule plus a review to enforce it — including the conditions under which the decision should be revisited.
- Which case would you argue is strong enough to justify the construct even in a mixed-engine codebase?A per-row lookup feeding several output columns. The portable workaround is repeated scalar subqueries, which can select different rows when the ordering is not total and therefore emit records that never existed. Preventing a correctness defect outweighs the cost of maintaining a second spelling; a merely tidier query does not.
- How do you keep two spellings of the same query from drifting apart?Keep them adjacent — one module holding both variants behind a single named operation — and cover the operation with tests that run on each target engine. Drift happens when the alternative spelling lives in a distant file that nobody edits when the main query changes.
- What operational risk would you call out when introducing the pattern to a team?That the inner form drops left rows whose subquery returned no rows, so a report loses entities silently rather than failing. Make the choice between CROSS JOIN LATERAL and LEFT JOIN LATERAL ... ON TRUE an explicit review question, and test each new query against a group with no detail rows.
saying these in an interview costs you the question
- Assuming every SQL engine supports the LATERAL keyword
- Treating the choice as personal style rather than portability
- Ignoring test and development engines in the support matrix
- Adopting it without teaching that the inner form drops rows
- Scattering engine-specific spellings throughout the codebase