SQL LATERAL Joins & CROSS APPLY: When They Beat Subqueries

sql lateral join is the single most under-taught primitive in modern SQL — and the single largest tool a senior data engineer can pull out of the ANSI SQL/1999 standard when the plain correlated subquery hits a wall. Once you internalise "LATERAL means: for each outer row, run this inner query with access to the outer columns," a whole family of otherwise-painful queries collapses into a two-line skeleton — the classic sql top-n per group ("give me the last three orders per customer"), JSON and array unnest with correlation, table-valued function composition, and every other "run a mini-query per row" ask an interviewer might throw at a whiteboard.

This guide is the mid-to-senior tour you wished existed the first time a reviewer asked why your query was doing an N × subquery scan, when an interviewer probed the difference between lateral vs subquery semantics, when the team lead demanded a cross apply sql server port of your postgres lateral recipe, when the analyst asked whether bigquery lateral was a thing, or when the on-call engineer showed a outer apply plan running 10× faster than the LEFT JOIN + ROW_NUMBER() alternative. It walks through the LATERAL syntax and mental model (LEFT JOIN LATERAL vs CROSS JOIN LATERAL, correlation binding, set-returning-function unnest), the CROSS APPLY / OUTER APPLY family that SQL Server ships as its LATERAL equivalent (the translation table, APPLY + TVF composition, JSON_TABLE / OPENJSON unnest), the top-N-per-group performance story (LATERAL + LIMIT with an index seek versus ROW_NUMBER() OVER with a sort), and the six-engine performance matrix (Postgres, MySQL 8, Oracle 12c+, SQL Server, Snowflake, BigQuery) so you always know which keyword to reach for on which warehouse. Every section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL joins practice library →, rehearse on top-N-per-group problems →, and sharpen the correlated-subquery axis with the subqueries drill room →.

---

On this page

• Why LATERAL matters in 2026
• LATERAL syntax & mental model
• CROSS APPLY & OUTER APPLY
• Top-N per group pattern
• When LATERAL beats subquery — perf matrix
• Cheat sheet — LATERAL / CROSS APPLY recipe list
• Frequently asked questions
• Practice on PipeCode

---

1. Why LATERAL matters in 2026

The "for each outer row, run this inner query" primitive — the missing piece between joins, subqueries, and window functions

The one-sentence invariant: a sql lateral join runs the right-hand subquery once per row of the left-hand relation, with the inner query allowed to reference the outer row's columns — turning "SELECT the top-3 orders per customer" from a window-function-plus-filter puzzle into a two-line correlated join. Once you internalise "for each outer row, run this query," the LATERAL interview surface collapses into a single mental model with a handful of dialect variants.

Where LATERAL fits in the join family.

• Inner join / outer join. Rows are matched on an equality (or range) predicate. Both sides are evaluated independently, then the planner picks a join algorithm — hash, merge, or nested loop.
• Correlated subquery in the SELECT list. For each outer row, the planner runs the scalar subquery to produce one column value. Cost is N × cost(subquery) in the worst case — many planners fail to unnest correlated scalars.
• Correlated subquery in the WHERE / EXISTS clause. For each outer row, the planner runs the boolean subquery to filter. Modern planners try hard to rewrite these to a semi-join, but a rewrite is not guaranteed.
• LATERAL / CROSS APPLY. For each outer row, the planner runs a table-producing subquery — and joins the outer row to each of the inner rows returned. The inner query can reference outer columns freely. Semantically, LATERAL is a correlated join that produces zero, one, or many inner rows per outer row.

Why the primitive is under-taught.

• The SQL/1999 standard introduced the keyword, but implementations lagged for over a decade. Postgres added it in 9.3 (2013), MySQL waited until 8.0.14 (2019), Oracle shipped it in 12c (2013), and SQL Server never adopted the keyword — instead offering CROSS APPLY and OUTER APPLY since 2005 with identical semantics.
• Every classic SQL textbook was written before LATERAL was widely available, so an entire generation of engineers learned to reach for WHERE (partition, ranked_col) IN (SELECT ...) or window-function-plus-filter patterns even when a two-line LATERAL would beat both.
• LATERAL is also invisible to the naive query author — you have to know the keyword to type it. There is no CTE-style rewrite that emerges from re-reading your own draft.

Dialect coverage in 2026 — the six engines you actually ship to.

• Postgres 9.3+. Full support. Reads exactly like the ANSI syntax — SELECT ... FROM outer, LATERAL (SELECT ... WHERE inner.k = outer.k) AS inner. Both CROSS JOIN LATERAL and LEFT JOIN LATERAL ... ON TRUE variants supported.
• MySQL 8.0.14+. Full support. Same syntax as Postgres. Older MySQL versions (5.7, 8.0.0–8.0.13) require the ROW_NUMBER() workaround.
• Oracle 12c+. Ships both LATERAL (ANSI) and CROSS APPLY / OUTER APPLY (SQL-Server-compatible). Interchangeable — pick the one your team reads more fluently.
• SQL Server 2005+. No LATERAL keyword. Uses CROSS APPLY (inner) and OUTER APPLY (left) instead. Same semantics as ANSI LATERAL.
• Snowflake. Full LATERAL support since 2020. Also supports the FLATTEN table function inside LATERAL for JSON / array unnest.
• BigQuery. No explicit LATERAL keyword. Correlation is implicit — an unnested array reference in the FROM clause behaves like CROSS JOIN LATERAL UNNEST(...). Scalar correlation is expressed via subqueries, not LATERAL.

Three problem shapes LATERAL nails.

• Top-N per group. "Latest 3 orders per customer" — the archetype. LATERAL + LIMIT 3 compiles to a nested-loop-with-limit plan that stops fetching inner rows the moment the third one arrives; ROW_NUMBER() OVER + WHERE rn ≤ 3 has to materialise every ranked row across the whole table before filtering.
• JSON / array unnest with correlation. "Unroll every item in the JSON payload, keeping the parent row." LATERAL + jsonb_array_elements(payload) in Postgres, CROSS APPLY OPENJSON(payload) in SQL Server. The unnest function must see the outer row's payload column — that correlation is exactly what LATERAL exists for.
• TVF composition. "Run this parametrised table-valued function per row of an input list." SELECT c.*, t.* FROM customers c CROSS APPLY dbo.top_orders(c.id) t on SQL Server; the same pattern with a Postgres SETOF-returning function inside LATERAL.

Why interviewers love LATERAL as a probe.

• It's a compact fluency check. A senior candidate reaches for LATERAL on the top-3-orders-per-customer question in the first minute; a junior reaches for ROW_NUMBER() OVER and has to be nudged toward the LATERAL rewrite.
• It forces mental-model precision. "What does the inner query see?" (the current outer row's columns) "What does the outer query see?" (each inner row emitted). "What if the inner returns zero rows?" (CROSS JOIN LATERAL drops the outer row; LEFT JOIN LATERAL keeps it with NULLs).
• It reveals planner intuition. "When does LATERAL beat a plain correlated subquery?" (when the planner can push a limit or unique into the inner scan). "When does it lose?" (when N is huge and the inner query can't be indexed).
• It exposes dialect awareness. "Would this run on SQL Server?" — a candidate who instantly answers "yes, but you write CROSS APPLY instead of CROSS JOIN LATERAL" signals cross-warehouse fluency.

Where LATERAL / APPLY lands in your pipelines.

• Analytics on wide fact tables. "Attach the most recent status change to each order," "attach the top three sessions per user," "attach the last five sensor readings to each device row." All classic top-N-per-group patterns.
• Feature engineering. "For each user row, compute the sum of the last 30 days of spend." LATERAL + windowed aggregate inside — the inner query filters by the outer's user_id and produces one summary row per outer.
• ETL of nested data. "Explode each JSON payload into its item rows, keeping the parent metadata." LATERAL + jsonb_array_elements / OPENJSON / UNNEST — one row per (parent, item) pair.
• Stored-procedure-to-SQL migrations. Legacy code with a WHILE loop that runs a query per row of a driver table maps directly to LATERAL / CROSS APPLY over that driver table.

What senior interviewers actually probe.

• Do you know the syntax? CROSS JOIN LATERAL (…) (inner) vs LEFT JOIN LATERAL (…) ON TRUE (left). The ON TRUE is a common typo target — every candidate forgets it once.
• Do you know the semantic difference? Inner drops outer rows with no inner match; left keeps them with NULLs. Same as regular join families.
• Do you know the SQL Server keyword? CROSS APPLY = CROSS JOIN LATERAL; OUTER APPLY = LEFT JOIN LATERAL ... ON TRUE. Interchangeable on Oracle.
• Do you know when LATERAL wins on perf? When the inner query is expensive per row and can be indexed with (partition_col, order_col) — the nested-loop-with-limit plan is dramatically cheaper than a sort-and-rank plan.
• Do you know the BigQuery variant? No LATERAL keyword; UNNEST in the FROM clause is implicitly correlated. For scalar-correlation asks, BigQuery falls back to subqueries.

Worked example — top-3 orders per customer, the first LATERAL query most engineers write

Detailed explanation. The archetype: given customers(id, name) and orders(id, customer_id, order_ts, total), return the three most recent orders per customer. This is the fastest way to spot whether a candidate has ever reached for LATERAL — the two-line answer is a fingerprint on the whiteboard.

Question. Write a Postgres query that returns, for each customer, the three most recent orders (order_ts, total). Use LATERAL. Assume there is an index on orders(customer_id, order_ts DESC).

Input.

customer_id	name
1	Alice
2	Bob
3	Carol

id	customer_id	order_ts	total
101	1	2026-07-01	40
102	1	2026-07-05	25
103	1	2026-07-08	90
104	1	2026-07-09	60
105	2	2026-07-02	70
106	2	2026-07-07	30

Code.

SELECT c.id AS customer_id, c.name, o.order_ts, o.total
FROM customers c
CROSS JOIN LATERAL (
  SELECT o.order_ts, o.total
  FROM orders o
  WHERE o.customer_id = c.id
  ORDER BY o.order_ts DESC
  LIMIT 3
) o
ORDER BY c.id, o.order_ts DESC;

Step-by-step explanation.

1. FROM customers c — the outer relation. Every row here is visited once.
2. CROSS JOIN LATERAL (…) o — for each outer row c, run the parenthesised subquery. The subquery reads c.id freely — that is the correlation binding.
3. Inside the LATERAL — WHERE o.customer_id = c.id ORDER BY o.order_ts DESC LIMIT 3. With the index on (customer_id, order_ts DESC), this is a single index seek plus a bounded scan of at most three rows.
4. CROSS JOIN LATERAL drops any customer with no matching orders. To keep customers with no orders, swap to LEFT JOIN LATERAL (…) ON TRUE and the missing-inner rows become NULL.
5. Final ORDER BY c.id, o.order_ts DESC produces a stable output — customers grouped, each customer's orders newest first.

Output.

customer_id	name	order_ts	total
1	Alice	2026-07-09	60
1	Alice	2026-07-08	90
1	Alice	2026-07-05	25
2	Bob	2026-07-07	30
2	Bob	2026-07-02	70

Rule of thumb. LATERAL + LIMIT K with a covering index is the fastest top-N-per-group plan on Postgres, MySQL 8, and Oracle. The nested-loop-with-limit stops early per customer, so total cost is K × outer_rows × log(inner_rows) — dramatically better than the ROW_NUMBER() OVER + WHERE rn ≤ 3 sort-then-filter plan on skewed data.

Worked example — LATERAL vs plain correlated subquery — where the semantics diverge

Detailed explanation. A common source of confusion — the difference between "correlated subquery in the SELECT list" (returns exactly one value) and "LATERAL subquery in the FROM clause" (returns zero-to-many rows). The interviewer wants to hear you say "correlated scalar subqueries are one-column-one-row; LATERAL is one-outer-row-to-many-inner-rows."

Question. Given the same tables, write two versions of "for each customer, get the most recent order total": one using a correlated scalar subquery, one using LATERAL. Explain the semantic difference.

Input. (Same as above.)

Code.

-- Version A: correlated scalar subquery in SELECT list
SELECT
  c.id AS customer_id,
  c.name,
  (SELECT o.total
   FROM orders o
   WHERE o.customer_id = c.id
   ORDER BY o.order_ts DESC
   LIMIT 1) AS last_total
FROM customers c;

-- Version B: LATERAL join
SELECT
  c.id AS customer_id,
  c.name,
  o.total AS last_total,
  o.order_ts AS last_order_ts
FROM customers c
LEFT JOIN LATERAL (
  SELECT o.total, o.order_ts
  FROM orders o
  WHERE o.customer_id = c.id
  ORDER BY o.order_ts DESC
  LIMIT 1
) o ON TRUE;

Step-by-step explanation.

1. Version A returns exactly one column (last_total) per customer. If the inner query returned two columns, the whole query would fail — the SELECT-list subquery must be scalar (one column, one row).
2. Version B returns many columns from the inner query (total, order_ts, and any others you project). The LATERAL join yields a full inner row per outer, projected alongside the outer columns.
3. Version A silently returns NULL for customers with no orders — the subquery returns zero rows, which the SELECT-list treats as NULL.
4. Version B with LEFT JOIN LATERAL ... ON TRUE behaves the same way — outer row preserved with NULL inner columns. With CROSS JOIN LATERAL, customers with no orders would be dropped entirely.
5. Perf-wise, most planners rewrite Version A to Version B under the hood — but "most" is not "all." Older planners and specific dialects (BigQuery on certain shapes) leave the correlated scalar as an N × subquery plan.

Output (both versions).

customer_id	name	last_total	last_order_ts
1	Alice	60	2026-07-09
2	Bob	30	2026-07-07
3	Carol	NULL	NULL

Rule of thumb. Reach for LATERAL the moment you need more than one column from the inner query per outer row, or when you want explicit control over the plan. Correlated scalar subqueries are fine for one-column-one-row lookups, but only when you trust the planner to unnest them.

Worked example — engine dialect quickstart

Detailed explanation. A quick sanity check the interviewer might drop: "which of these engines can I run LATERAL / APPLY on today?" The answer is a short six-row list — knowing it cold is a senior signal.

Question. Given a list of engines, mark each with the LATERAL / APPLY keyword it supports as of 2026.

Input. Postgres 16, MySQL 8.0.14+, Oracle 12c+, SQL Server 2022, Snowflake, BigQuery.

Code. (No code — a dialect matrix answer.)

Engine          | LATERAL support (as of 2026)              | Notes
Postgres 9.3+   | LATERAL (ANSI), CROSS/LEFT JOIN LATERAL   | first mover in OSS
MySQL 8.0.14+   | LATERAL (ANSI), CROSS/LEFT JOIN LATERAL   | 5.7 lacks it
Oracle 12c+     | LATERAL + CROSS APPLY + OUTER APPLY        | ships both keywords
SQL Server 2005+| CROSS APPLY + OUTER APPLY (no LATERAL kw)  | same semantics
Snowflake       | LATERAL, LATERAL FLATTEN                    | FLATTEN for JSON
BigQuery        | UNNEST is implicit LATERAL; no keyword     | correlated array only

Step-by-step explanation.

1. Postgres was the OSS first-mover in 2013. If you're on Postgres 12+, LATERAL is table stakes.
2. MySQL waited until 8.0.14 in 2019. If your project supports MySQL 5.7, LATERAL is off-limits; use the ROW_NUMBER() workaround.
3. Oracle 12c (2013) ships both keywords — LATERAL (ANSI) and CROSS/OUTER APPLY (SQL-Server-compatible). Pick whichever your team reads better.
4. SQL Server never added LATERAL. Every LATERAL query maps mechanically to CROSS APPLY (inner) or OUTER APPLY (left).
5. Snowflake ships LATERAL and adds LATERAL FLATTEN for VARIANT / JSON columns — the standard unnest pattern for semi-structured data.
6. BigQuery has no LATERAL keyword. Array unnest via UNNEST(...) in the FROM clause is implicitly correlated; scalar correlations use subqueries.

Output. The matrix above.

Rule of thumb. Memorise the six-row list. When an interviewer asks "would you use LATERAL here?" the first question back is "what's the warehouse?" — say it out loud and you signal senior instincts.

Senior interview question on the LATERAL mental model

A senior interviewer often opens with: "I hand you two tables — customers and orders — and ask for the two most-recent orders per customer. Walk me through the LATERAL query, tell me what changes on SQL Server, and tell me when this beats ROW_NUMBER() OVER + WHERE rn ≤ 2."

Solution Using CROSS JOIN LATERAL with LIMIT 2

-- ANSI / Postgres / MySQL 8 / Oracle 12c+ / Snowflake
SELECT c.id AS customer_id, c.name, o.order_ts, o.total
FROM customers c
CROSS JOIN LATERAL (
  SELECT o.order_ts, o.total
  FROM orders o
  WHERE o.customer_id = c.id
  ORDER BY o.order_ts DESC
  LIMIT 2
) o
ORDER BY c.id, o.order_ts DESC;

-- SQL Server 2005+ — same semantics, CROSS APPLY keyword
SELECT c.id AS customer_id, c.name, o.order_ts, o.total
FROM customers c
CROSS APPLY (
  SELECT TOP 2 o.order_ts, o.total
  FROM orders o
  WHERE o.customer_id = c.id
  ORDER BY o.order_ts DESC
) o
ORDER BY c.id, o.order_ts DESC;

Step-by-step trace.

Step	Outer row (customer_id)	Inner query result	Emitted rows
1	c1 = Alice	last 2 = (07-09, 60), (07-08, 90)	(Alice, 07-09, 60), (Alice, 07-08, 90)
2	c2 = Bob	last 2 = (07-07, 30), (07-02, 70)	(Bob, 07-07, 30), (Bob, 07-02, 70)
3	c3 = Carol	last 2 = (empty)	(dropped — CROSS JOIN LATERAL)
4	Sort	ORDER BY c.id, o.order_ts DESC	Final output

The engine walks each customer, runs the inner LIMIT 2 query with the outer's c.id bound in, and emits at most two rows per customer. Carol has no orders — the CROSS JOIN LATERAL drops her; swap to LEFT JOIN LATERAL ... ON TRUE to preserve her with NULLs.

Output:

customer_id	name	order_ts	total
1	Alice	2026-07-09	60
1	Alice	2026-07-08	90
2	Bob	2026-07-07	30
2	Bob	2026-07-02	70

Why this works — concept by concept:

• CROSS JOIN LATERAL binds the outer row into the inner query — inside the parentheses, c.id is a live value from the current outer row, not a table reference. The planner treats this as a correlated join, not a Cartesian product.
• LIMIT 2 is a per-outer-row limit, not a global limit — the LATERAL subquery runs once per outer row and each run applies its own LIMIT. This is why you can't achieve the same result with a plain SELECT ... LIMIT 2 at the top level.
• Index seek + bounded scan — with an index on (customer_id, order_ts DESC), each inner run is one seek (position on the customer_id) plus at most two forward reads. Total cost is outer_rows × (seek + 2 × row_fetch) — dramatically cheaper than sorting all orders.
• CROSS APPLY on SQL Server is a syntactic rebrand — the semantics are identical to CROSS JOIN LATERAL. The TOP 2 keyword replaces LIMIT 2; everything else is the same query.
• Cost — O(N_c × (log N_o + K)) where N_c is customer count, N_o is order count, and K = 2 is the LIMIT. Compare with ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_ts DESC), which is O(N_o log N_o) for the sort — bad when N_o is huge and N_c is small. LATERAL wins on skewed data with a good index; the window function wins when the (customer, order) distribution is uniform and no index is available.

SQL
Topic — top-N per group
Top-N per group problems

Practice →

SQL Topic — joins (SQL) SQL joins — full library

Practice →

---

2. LATERAL syntax & mental model

postgres lateral and lateral vs subquery — CROSS JOIN LATERAL, LEFT JOIN LATERAL, and the correlation binding that makes it all work

The mental model in one line: LATERAL turns the right-hand FROM-clause subquery into a per-outer-row invocation, letting the inner query freely reference outer columns and producing zero, one, or many rows per outer — behaving like an inner join (CROSS JOIN LATERAL) or a left join (LEFT JOIN LATERAL … ON TRUE) depending on the keyword. Once you say "for each outer row, run this inner query and either drop or preserve the outer row based on whether the inner returned anything," the LATERAL syntax interview surface reduces to a two-slot template.

Slot 1 — the join keyword.

• CROSS JOIN LATERAL (…) alias — inner-join semantics. If the inner subquery returns zero rows, the outer row is dropped from the result. Equivalent to CROSS APPLY on SQL Server / Oracle.
• LEFT JOIN LATERAL (…) alias ON TRUE — left-outer-join semantics. If the inner subquery returns zero rows, the outer row is preserved with NULLs in the inner columns. Equivalent to OUTER APPLY on SQL Server / Oracle.
• INNER JOIN LATERAL (…) alias ON TRUE — spelled out; identical to CROSS JOIN LATERAL on most engines.
• RIGHT JOIN LATERAL — not defined. LATERAL is asymmetric — the inner references the outer, not vice versa. There's no way to reverse the direction.

Slot 2 — the inner subquery.

• Any SELECT that can reference the outer columns. Includes CTEs (WITH … SELECT …), set-returning function calls (SELECT * FROM generate_series(1, outer.n)), unnest expressions (SELECT * FROM jsonb_array_elements(outer.payload)), table-valued function calls (Oracle / SQL Server), and window-function subqueries.
• Returns zero, one, or many rows. Multiple columns supported — the LATERAL is a table, not a scalar.
• Can be aliased — LATERAL (…) AS o — with column aliases in the ANSI form LATERAL (…) AS o(col1, col2).

Correlation binding — the rule that makes LATERAL a "join" instead of a "cross product".

• The inner query sees the outer row as if it were a set of parameters. Any column of the outer table can appear inside the LATERAL subquery.
• Without the LATERAL keyword, an ordinary subquery in the FROM clause cannot reference outer columns — a query like FROM customers c, (SELECT * FROM orders WHERE customer_id = c.id) o is a syntax error on strict parsers.
• The LATERAL keyword tells the parser "this subquery is allowed to see the outer scope." On Postgres, LATERAL is required whenever the subquery references outer columns from the same FROM clause.
• On Postgres, LATERAL is implicit on set-returning functions in the FROM clause — SELECT * FROM t, unnest(t.arr) works without the keyword. Being explicit is the safer style.

Two syntactic forms on Postgres — comma vs JOIN.

• Comma form — FROM outer, LATERAL (…) inner — equivalent to CROSS JOIN LATERAL. The comma is a cross join by default.
• JOIN form — FROM outer CROSS JOIN LATERAL (…) inner or FROM outer LEFT JOIN LATERAL (…) inner ON TRUE. The JOIN form is more explicit and preferred for review clarity.
• Both compile to the same plan. The JOIN form is what most modern style guides recommend.

Common newbie mistakes.

• Forgetting ON TRUE on LEFT JOIN LATERAL. Every LEFT JOIN needs an ON clause; the LATERAL subquery has no join column to reference, so the correct spelling is LEFT JOIN LATERAL (…) x ON TRUE.
• Omitting the LATERAL keyword. Without it, the outer columns are invisible inside the subquery and the query errors out.
• Assuming the inner query is executed once. It runs once per outer row — the whole point of LATERAL.
• Assuming the outer query can see the inner. It can't — the outer projects only its own columns plus whatever the LATERAL alias exposes.
• Placing an aggregate outside the LATERAL block expecting per-outer aggregation. Aggregates outside operate on the joined result set; per-outer aggregation belongs inside the LATERAL.

How LATERAL interacts with GROUP BY and window functions.

• GROUP BY outer_col after a LATERAL join still works — the group is over the joined result set.
• ROW_NUMBER() OVER (PARTITION BY outer_col) after a LATERAL is redundant if the LATERAL already used LIMIT K internally.
• Aggregates inside the LATERAL (SELECT SUM(x) FROM inner WHERE inner.k = outer.k) produce one summary row per outer — equivalent to a correlated subquery with better plan predictability.

Set-returning functions inside LATERAL — the JSON / array pattern.

• Postgres: SELECT t.id, e.value FROM t, LATERAL jsonb_array_elements(t.payload) e — one row per (parent, JSON element) pair.
• Postgres: SELECT t.id, u.n FROM t, LATERAL unnest(t.arr) u(n) — one row per (parent, array element) pair.
• Postgres: SELECT o.id, r FROM orders o, LATERAL generate_series(1, o.qty) r — one row per (order, series element) — useful for expanding aggregate quantities into rows.
• Snowflake: SELECT t.id, f.value FROM t, LATERAL FLATTEN(input => t.payload) f — the VARIANT / JSON unnest primitive.

Interview probes on syntax fluency.

• "Why CROSS JOIN LATERAL and not just a plain subquery?" — the plain subquery in the FROM clause cannot reference outer columns without LATERAL; LATERAL is the keyword that authorises correlation.
• "When would you switch to LEFT JOIN LATERAL?" — whenever you must preserve outer rows even if the inner returns nothing. Reporting "one row per customer, latest order or NULL" is the canonical case.
• "What's the difference between LATERAL and a CTE?" — a CTE is evaluated once, produces one result set, and joins to the outer table by its own predicates. A LATERAL runs once per outer row and is naturally correlated. CTEs can't express per-row-limited inner queries the way LATERAL can.
• "Why does Postgres require the keyword but BigQuery not?" — Postgres is strict about correlation scope; BigQuery's UNNEST on an array column in the FROM clause is implicitly LATERAL because the array reference is inherently correlated to its parent row.

Worked example — LATERAL vs a lateral-less rewrite

Detailed explanation. The clearest way to see what LATERAL adds: try the same query without it. On Postgres the lateral-less form errors out because the inner subquery can't see the outer columns. Writing both side-by-side is the fastest way to internalise the correlation-binding rule.

Question. Given customers and orders, get the most recent order per customer. Write two Postgres queries — one with LATERAL, one attempting the same without LATERAL — and explain why the lateral-less version fails.

Input. (Same customers and orders as the first section.)

Code.

-- With LATERAL — compiles
SELECT c.id, c.name, o.order_ts, o.total
FROM customers c
LEFT JOIN LATERAL (
  SELECT order_ts, total
  FROM orders
  WHERE customer_id = c.id
  ORDER BY order_ts DESC
  LIMIT 1
) o ON TRUE;

-- Without LATERAL — ERROR: invalid reference to FROM-clause entry
SELECT c.id, c.name, o.order_ts, o.total
FROM customers c
LEFT JOIN (
  SELECT order_ts, total
  FROM orders
  WHERE customer_id = c.id           -- ERROR: no c.id in scope
  ORDER BY order_ts DESC
  LIMIT 1
) o ON TRUE;

Step-by-step explanation.

1. The lateral-less version places the subquery in the FROM clause but without the LATERAL keyword. Postgres treats it as an independent subquery whose scope is only its own FROM clause — c.id is not visible.
2. The parser rejects the query with ERROR: invalid reference to FROM-clause entry for table "c". On MySQL 8 the same error appears as Unknown column 'c.id' in 'where clause'.
3. Adding LATERAL to the second query fixes it — the keyword authorises the correlation.
4. Without LATERAL, the equivalent trick is a correlated scalar subquery in the SELECT list or a ROW_NUMBER() + filter — both work, both compile, both have different perf characteristics.
5. The rule of thumb — if your subquery in the FROM clause needs to see the outer row's columns, you need LATERAL. Every time.

Output.

id	name	order_ts	total
1	Alice	2026-07-09	60
2	Bob	2026-07-07	30
3	Carol	NULL	NULL

Rule of thumb. LATERAL is the keyword that turns a "same-level subquery" into a "correlated join." Without it, subquery scopes are isolated; with it, the inner query sees the outer row like function parameters.

Worked example — LEFT JOIN LATERAL ... ON TRUE for preserving outer rows

Detailed explanation. The most common LATERAL variant in production analytics — "one row per customer, plus the latest order or NULLs if there isn't one." The ON TRUE looks weird until you realise every LEFT JOIN needs an ON clause and there's no natural join column between a LATERAL block and its outer.

Question. Write a Postgres query that returns every customer along with their most recent order (or NULLs for customers who never ordered). Sort by customer id.

Input. (Same customers and orders as above.)

Code.

SELECT
  c.id AS customer_id,
  c.name,
  o.id AS last_order_id,
  o.order_ts AS last_order_ts,
  o.total AS last_order_total
FROM customers c
LEFT JOIN LATERAL (
  SELECT o.id, o.order_ts, o.total
  FROM orders o
  WHERE o.customer_id = c.id
  ORDER BY o.order_ts DESC
  LIMIT 1
) o ON TRUE
ORDER BY c.id;

Step-by-step explanation.

1. LEFT JOIN LATERAL — LEFT-outer semantics. If the inner subquery returns zero rows, the outer row is preserved with NULLs in the inner columns.
2. ON TRUE — every LEFT JOIN needs an ON clause. The LATERAL has no natural join column (the correlation happens inside the parentheses), so ON TRUE says "match unconditionally on whatever the inner emits."
3. Inner subquery — one row per outer via LIMIT 1. Reads c.id from the outer scope.
4. Carol has no orders — the inner returns zero rows — the LEFT JOIN LATERAL preserves Carol with NULLs in last_order_id, last_order_ts, last_order_total.
5. Alice and Bob each have orders — the inner returns exactly one row — the LEFT JOIN LATERAL emits the pair.

Output.

customer_id	name	last_order_id	last_order_ts	last_order_total
1	Alice	104	2026-07-09	60
2	Bob	106	2026-07-07	30
3	Carol	NULL	NULL	NULL

Rule of thumb. LEFT JOIN LATERAL (…) x ON TRUE is the canonical "outer preserved, inner optional" pattern. Every reporting query with a "most recent event per entity" ask uses this shape.

Worked example — LATERAL over a set-returning function

Detailed explanation. The killer LATERAL pattern for semi-structured data — unnesting arrays or JSON per outer row. Postgres exposes unnest, jsonb_array_elements, generate_series; Snowflake exposes FLATTEN; MySQL uses JSON_TABLE. All read cleanly inside a LATERAL.

Question. Given orders(id, items jsonb) where items is a JSON array of {sku, qty, price} objects, unroll each item into its own row alongside the parent order id.

Input.

id	items
1	[{"sku":"A","qty":2,"price":10},{"sku":"B","qty":1,"price":25}]
2	[{"sku":"C","qty":3,"price":5}]

Code.

SELECT
  o.id AS order_id,
  item ->> 'sku' AS sku,
  (item ->> 'qty')::int AS qty,
  (item ->> 'price')::numeric AS price
FROM orders o
CROSS JOIN LATERAL jsonb_array_elements(o.items) AS item
ORDER BY o.id, sku;

Step-by-step explanation.

1. jsonb_array_elements(o.items) is a set-returning function that produces one row per element of the JSON array — perfect for unnesting.
2. Inside the LATERAL, o.items is bound from the outer row. Order 1 emits two elements; order 2 emits one.
3. CROSS JOIN LATERAL — inner-join semantics. Orders with an empty or NULL items array would be dropped; swap to LEFT JOIN LATERAL … ON TRUE to preserve them.
4. Outside the LATERAL, we destructure each element with ->> (returns text) and cast to int / numeric as needed.
5. Snowflake equivalent: LATERAL FLATTEN(input => o.items) f then f.value:sku::string, f.value:qty::int. SQL Server: CROSS APPLY OPENJSON(o.items) WITH (sku VARCHAR(50), qty INT, price DECIMAL(10,2)).

Output.

order_id	sku	qty	price
1	A	2	10
1	B	1	25
2	C	3	5

Rule of thumb. LATERAL + set-returning function is the way to unnest arrays and JSON in modern SQL. Every ETL pipeline that ingests semi-structured payloads uses this shape somewhere.

Senior interview question on LATERAL correlation

A senior interviewer might ask: "Write a Postgres query that, for each customer, returns the customer's id, the total number of orders they've placed, the total spend, and the details of their most recent order in one row. Use LATERAL and explain why this beats three separate correlated subqueries."

Solution Using LEFT JOIN LATERAL with per-outer aggregate + top-1

SELECT
  c.id                                 AS customer_id,
  c.name,
  agg.order_count,
  agg.total_spend,
  last_ord.id                          AS last_order_id,
  last_ord.order_ts                    AS last_order_ts,
  last_ord.total                       AS last_order_total
FROM customers c
LEFT JOIN LATERAL (
  SELECT COUNT(*) AS order_count, COALESCE(SUM(total), 0) AS total_spend
  FROM orders
  WHERE customer_id = c.id
) agg ON TRUE
LEFT JOIN LATERAL (
  SELECT id, order_ts, total
  FROM orders
  WHERE customer_id = c.id
  ORDER BY order_ts DESC
  LIMIT 1
) last_ord ON TRUE
ORDER BY c.id;

Step-by-step trace.

Outer	agg subquery result	last_ord subquery result	Emitted row
c1 = Alice	(4, 215)	(104, 07-09, 60)	(1, Alice, 4, 215, 104, 07-09, 60)
c2 = Bob	(2, 100)	(106, 07-07, 30)	(2, Bob, 2, 100, 106, 07-07, 30)
c3 = Carol	(0, 0)	(NULL, NULL, NULL)	(3, Carol, 0, 0, NULL, NULL, NULL)

Two LATERAL blocks per outer row — one aggregates, the other picks the top-1. Both are LEFT-outer, so customers with zero orders still appear (agg returns (0, 0) from the empty set with COALESCE; last_ord returns zero rows so the LEFT JOIN emits NULLs).

Output:

customer_id	name	order_count	total_spend	last_order_id	last_order_ts	last_order_total
1	Alice	4	215	104	2026-07-09	60
2	Bob	2	100	106	2026-07-07	30
3	Carol	0	0	NULL	NULL	NULL

Why this works — concept by concept:

• One LATERAL block per per-outer computation — the aggregation lives in one LATERAL, the top-1 lookup lives in another. Each block runs once per outer row with the outer's c.id bound in. Adding a third computation adds a third LATERAL — the pattern composes cleanly.
• COALESCE inside the aggregate LATERAL — SUM(total) on an empty result set is NULL. Wrapping it in COALESCE(..., 0) turns Carol's spend into a proper zero instead of a NULL. This is idiomatic for "count-plus-sum-with-zero-default" reports.
• LEFT JOIN LATERAL for the top-1 lookup — Carol has no orders, so the top-1 subquery returns zero rows. LEFT JOIN LATERAL preserves her outer row with NULLs; CROSS JOIN LATERAL would drop her, which is wrong for a "one row per customer" report.
• Three separate correlated scalars would be slower — one (SELECT COUNT(*) FROM orders WHERE …) in the SELECT list plus one (SELECT SUM(total) FROM …) plus one (SELECT id FROM … ORDER BY … LIMIT 1) is three inner passes per outer. Two LATERAL blocks is two inner passes — the aggregate combines COUNT and SUM in one pass — and the plan is more predictable.
• Cost — O(N_c × (P_agg + P_top1)) where P_agg is the cost of the aggregate scan per customer (index on (customer_id) gives O(matching_rows)) and P_top1 is the cost of the top-1 seek (index on (customer_id, order_ts DESC) gives O(log N_o)). With both indexes, the whole query is O(N_c × (matching_rows + log N_o)) — much better than three uncorrelated table scans.

SQL
Topic — joins
Joins problem library

Practice →

SQL Topic — subqueries Subquery drills

Practice →

---

3. CROSS APPLY & OUTER APPLY

cross apply sql server and outer apply — SQL Server's LATERAL equivalent, the translation table, and TVF composition

The mental model in one line: CROSS APPLY is SQL Server's spelling of CROSS JOIN LATERAL, and OUTER APPLY is its spelling of LEFT JOIN LATERAL ... ON TRUE — identical semantics with a different keyword, plus a killer composition trick with table-valued functions (TVFs) that turns "run this parametrised query per row" into a first-class citizen. Once you know the two-line translation table, every ANSI LATERAL recipe maps cleanly to SQL Server.

The translation table — every ANSI LATERAL maps to an APPLY.

• CROSS JOIN LATERAL (…) alias → CROSS APPLY (…) alias. Inner-join semantics; drops outer row when inner returns zero.
• LEFT JOIN LATERAL (…) alias ON TRUE → OUTER APPLY (…) alias. Left-outer semantics; preserves outer row with NULLs when inner returns zero.
• SELECT * FROM t, LATERAL (…) → SELECT * FROM t CROSS APPLY (…). Same semantics.
• LIMIT K inside the inner → TOP K inside the inner (SQL Server uses TOP, not LIMIT).
• jsonb_array_elements(t.payload) (Postgres) → OPENJSON(t.payload) (SQL Server 2016+).
• Set-returning functions → CROSS APPLY of a TVF or of OPENJSON / STRING_SPLIT / OPENROWSET.

Why SQL Server has APPLY instead of LATERAL.

• Microsoft shipped CROSS APPLY and OUTER APPLY in SQL Server 2005 — years before Postgres, MySQL, or Oracle added LATERAL. Once the keyword was baked into the SQL Server dialect, changing it would break every stored procedure ever written.
• Semantically APPLY and LATERAL are equivalent. There is no operation you can do in ANSI LATERAL that you can't do with CROSS APPLY / OUTER APPLY.
• Oracle 12c (2013) shipped both keywords for portability — ANSI LATERAL for standards-compliance and CROSS/OUTER APPLY for teams migrating from SQL Server.

APPLY + TVF — the composition superpower.

• A table-valued function (TVF) is a user-defined function that returns a table. SQL Server, Oracle, and Postgres all support them (Postgres calls them SETOF-returning functions).
• The killer pattern — SELECT c.*, t.* FROM customers c CROSS APPLY dbo.top_n_orders(c.id, 3) t — invokes the TVF once per outer row, passing the outer's c.id and a fixed 3 as arguments. The TVF returns a table; the outer row is joined to every row it emits.
• This turns a piece of reusable business logic (top_n_orders) into a plug-and-play building block. Callers get the correlated behaviour for free.
• Postgres equivalent — LATERAL (SELECT * FROM top_n_orders(c.id, 3)) or, if the function is SETOF orders, just LATERAL top_n_orders(c.id, 3) t.

JSON unnest via APPLY on SQL Server.

• OPENJSON(json_column) is SQL Server's JSON parser. Called inside CROSS APPLY, it returns one row per JSON element / property, with columns you specify via WITH (…).
• CROSS APPLY OPENJSON(o.items) WITH (sku VARCHAR(50), qty INT, price DECIMAL(10,2)) — reads exactly like Postgres's jsonb_array_elements with per-column extraction.
• For older SQL Server (pre-2016), the equivalent is a custom split TVF or STUFF + FOR XML PATH string manipulation — much uglier.

OPENJSON / OPENROWSET / STRING_SPLIT with APPLY — the SQL Server unnest family.

• OPENJSON(json) — unnest a JSON string or JSON column. Returns key/value pairs by default; with WITH (…) returns typed columns.
• STRING_SPLIT(delimited_string, delimiter) — unnest a delimited string. Returns one row per token.
• OPENROWSET(BULK 'file.csv', ...) — read an external file. Not a LATERAL primitive per se, but often composed with APPLY.
• All three are commonly used with CROSS APPLY to unnest a column into rows per parent.

Oracle's LATERAL + APPLY duality.

• Oracle 12c ships both keywords. LATERAL (…) and CROSS APPLY (…) are interchangeable; LEFT JOIN LATERAL (…) ON TRUE and OUTER APPLY (…) are also interchangeable.
• Most Oracle style guides prefer LATERAL for ANSI-conformance and use APPLY only when porting SQL Server code.
• Oracle also supports TABLE(function_name(args)) inside a FROM clause — a legacy syntax for calling PL/SQL table functions, superseded by the LATERAL / APPLY approach.

Common APPLY gotchas.

• TOP N inside CROSS APPLY without ORDER BY — non-deterministic. Always pair TOP N with ORDER BY.
• Forgetting to include the outer alias — CROSS APPLY (SELECT TOP 3 * FROM orders WHERE customer_id = c.id ORDER BY order_ts DESC) o — the reference to c.id must live inside the parentheses, not at the top level.
• Using SELECT * inside APPLY that returns overlapping column names — SQL Server may error on ambiguous columns.
• Using APPLY in an old compatibility level — APPLY is available from 2005 onwards, so any modern SQL Server supports it, but SQL Server 2000 databases upgraded in-place without changing compatibility level may still have issues.

Perf story — APPLY vs alternatives on SQL Server.

• APPLY is often faster than WITH cte AS (… ROW_NUMBER() OVER (…)) SELECT WHERE rn <= K when a covering index on (partition_col, order_col) exists. The APPLY plan seeks per outer row and stops early; the CTE plan sorts every row.
• APPLY beats correlated scalar subquery when you need multiple columns from the inner query.
• APPLY loses to a plain hash join when the join is a simple equality on a bounded inner set — the planner picks hash by default anyway.

Which APPLY variant when.

• CROSS APPLY — use when you want inner-join semantics. Drop outer rows with no matching inner rows.
• OUTER APPLY — use for reporting queries that need "one row per outer, plus optional inner." Every "customer report with most-recent-order-or-NULL" query uses OUTER APPLY.
• Never use CROSS APPLY when you need to preserve outer rows — you'll silently lose data.

Worked example — LATERAL → CROSS APPLY translation of the top-3-orders query

Detailed explanation. The most common port — take a working Postgres LATERAL query and rewrite it for SQL Server. The translation is mechanical.

Question. Rewrite the top-3-orders-per-customer LATERAL query as SQL Server CROSS APPLY.

Input. (Same customers and orders as before.)

Code.

-- Postgres: LATERAL
SELECT c.id, c.name, o.order_ts, o.total
FROM customers c
CROSS JOIN LATERAL (
  SELECT order_ts, total
  FROM orders
  WHERE customer_id = c.id
  ORDER BY order_ts DESC
  LIMIT 3
) o
ORDER BY c.id, o.order_ts DESC;

-- SQL Server: CROSS APPLY
SELECT c.id, c.name, o.order_ts, o.total
FROM customers c
CROSS APPLY (
  SELECT TOP 3 order_ts, total
  FROM orders
  WHERE customer_id = c.id
  ORDER BY order_ts DESC
) o
ORDER BY c.id, o.order_ts DESC;

Step-by-step explanation.

1. Keyword swap — CROSS JOIN LATERAL becomes CROSS APPLY. The parentheses stay the same.
2. Limit keyword — LIMIT 3 becomes TOP 3 at the beginning of the SELECT. SQL Server has no LIMIT.
3. ORDER BY inside the inner query stays. SQL Server requires it for deterministic TOP results.
4. Correlation binding — the reference to c.id inside the inner query works identically. APPLY authorises correlation just like LATERAL.
5. Outer ORDER BY at the end works the same way.

Output. (Same as the LATERAL version.)

Rule of thumb. Every LATERAL query maps to CROSS APPLY / OUTER APPLY with two keyword swaps. Keep the same mental model; change only the words.

Worked example — CROSS APPLY + inline TVF for reusable per-row logic

Detailed explanation. The composition win — wrap the per-row logic in a TVF, then compose it via CROSS APPLY. Every caller gets the correlated behaviour with one keyword.

Question. Create an inline TVF dbo.top_n_orders(@cust INT, @n INT) that returns the top-N most-recent orders for a customer, then use CROSS APPLY to fetch the top-3 for every customer.

Input. (Same as before.)

Code.

-- TVF definition (SQL Server 2008+)
CREATE FUNCTION dbo.top_n_orders (@cust INT, @n INT)
RETURNS TABLE
AS RETURN
  SELECT TOP (@n) id, order_ts, total
  FROM orders
  WHERE customer_id = @cust
  ORDER BY order_ts DESC;

-- Caller uses CROSS APPLY to invoke the TVF per outer row
SELECT c.id AS customer_id, c.name, t.id AS order_id, t.order_ts, t.total
FROM customers c
CROSS APPLY dbo.top_n_orders(c.id, 3) t
ORDER BY c.id, t.order_ts DESC;

Step-by-step explanation.

1. CREATE FUNCTION … RETURNS TABLE AS RETURN <SELECT> defines an inline TVF — the body is a single SELECT that returns a table.
2. Inline TVFs are optimised alongside the caller — the planner inlines the function body and rewrites it as if you'd typed the SELECT inline. No function-call overhead.
3. CROSS APPLY dbo.top_n_orders(c.id, 3) t — for each customer row, invoke the TVF with the outer's c.id and the constant 3. The TVF returns the top-3 orders; APPLY joins them to the outer row.
4. Downstream callers use one keyword — CROSS APPLY top_n_orders(...) — instead of duplicating the ORDER BY + TOP + WHERE. Business logic centralised.
5. Compare with multi-statement TVFs (RETURNS @t TABLE(...) AS BEGIN INSERT INTO @t SELECT ... END) — these are NOT inlined and have significantly worse perf. Prefer inline TVFs.

Output.

customer_id	name	order_id	order_ts	total
1	Alice	104	2026-07-09	60
1	Alice	103	2026-07-08	90
1	Alice	102	2026-07-05	25
2	Bob	106	2026-07-07	30
2	Bob	105	2026-07-02	70

Rule of thumb. Inline TVFs + CROSS APPLY is the SQL Server pattern for reusable per-row logic. One function definition, N callers, zero copy-paste.

Worked example — OPENJSON unnest with CROSS APPLY

Detailed explanation. The JSON unnest story on SQL Server. OPENJSON is a table-valued function that parses JSON and emits rows; CROSS APPLY runs it per parent row.

Question. Given orders(id, items NVARCHAR(MAX)) where items is a JSON array of {sku, qty, price} objects, unroll each item into its own row.

Input.

id	items
1	[{"sku":"A","qty":2,"price":10},{"sku":"B","qty":1,"price":25}]
2	[{"sku":"C","qty":3,"price":5}]

Code.

SELECT
  o.id       AS order_id,
  j.sku,
  j.qty,
  j.price
FROM orders o
CROSS APPLY OPENJSON(o.items)
  WITH (
    sku   VARCHAR(50)     '$.sku',
    qty   INT             '$.qty',
    price DECIMAL(10, 2)  '$.price'
  ) AS j
ORDER BY o.id, j.sku;

Step-by-step explanation.

1. OPENJSON(o.items) parses the JSON string in o.items and returns one row per element of the top-level array.
2. WITH (col type '$.path', ...) — the "explicit-schema" form of OPENJSON. Each column extracts a JSON path from the current element. Types are enforced.
3. CROSS APPLY runs OPENJSON once per outer order row, passing o.items in. Order 1 emits two element rows; order 2 emits one.
4. WITH is important — without it, OPENJSON returns a key/value/type triplet per element, which is much harder to use. Always use the explicit-schema form for typed unnest.
5. The equivalent on Postgres — jsonb_array_elements(o.items) in LATERAL, then ->> 'sku' etc. Snowflake — LATERAL FLATTEN(input => o.items) then f.value:sku::string.

Output.

order_id	sku	qty	price
1	A	2	10.00
1	B	1	25.00
2	C	3	5.00

Rule of thumb. CROSS APPLY OPENJSON(json_col) WITH (col type '$.path', ...) is the SQL Server pattern for JSON unnest. Every ETL that lands JSON payloads uses this shape.

Senior interview question on APPLY composition

A senior interviewer might ask: "Design a reporting query on SQL Server that, for each product, returns the top-3 orders by revenue and the total revenue across all orders. Use CROSS APPLY for the top-3, an inline TVF for reuse, and OUTER APPLY for the aggregate so products with no orders still appear."

Solution Using inline TVF + CROSS APPLY + OUTER APPLY composition

-- Inline TVF for top-N by revenue
CREATE FUNCTION dbo.top_n_orders_by_rev (@prod INT, @n INT)
RETURNS TABLE
AS RETURN
  SELECT TOP (@n) id, order_ts, qty * price AS revenue
  FROM order_lines
  WHERE product_id = @prod
  ORDER BY qty * price DESC;

-- Reporting query
SELECT
  p.id            AS product_id,
  p.name          AS product_name,
  t.id            AS top_order_id,
  t.order_ts      AS top_order_ts,
  t.revenue       AS top_order_revenue,
  agg.total_rev,
  agg.order_count
FROM products p
CROSS APPLY dbo.top_n_orders_by_rev(p.id, 3) t
OUTER APPLY (
  SELECT
    COUNT(*)                    AS order_count,
    COALESCE(SUM(qty * price), 0) AS total_rev
  FROM order_lines
  WHERE product_id = p.id
) agg
ORDER BY p.id, t.revenue DESC;

Step-by-step trace.

Step	Outer (product_id)	CROSS APPLY t	OUTER APPLY agg	Emitted rows
1	p1	top 3 = (o11, o12, o13)	(order_count=8, total_rev=520)	3 rows joined with the agg per row
2	p2	top 3 = (o21, o22) (only 2 orders)	(order_count=2, total_rev=90)	2 rows joined
3	p3	top 3 = (empty)	(order_count=0, total_rev=0)	0 rows (CROSS APPLY drops)

The CROSS APPLY t drops products with no orders — desirable for the "top order per product" report. The OUTER APPLY agg would preserve the outer row if it were the only join; since it's paired with CROSS APPLY t, the outer row is only emitted when t emits at least one inner row.

Output:

product_id	product_name	top_order_id	top_order_ts	top_order_revenue	total_rev	order_count
1	Widget A	o11	2026-07-10	250	520	8
1	Widget A	o12	2026-07-08	150	520	8
1	Widget A	o13	2026-07-05	100	520	8
2	Widget B	o21	2026-07-09	50	90	2
2	Widget B	o22	2026-07-06	40	90	2

Why this works — concept by concept:

• Inline TVF centralises the "top N by revenue" logic — one function definition, called from every reporting query that needs the pattern. The planner inlines the body, so there's zero function-call overhead.
• CROSS APPLY for the top-N, OUTER APPLY for the aggregate — CROSS APPLY drops products with zero orders (they shouldn't appear in a "top orders per product" report); OUTER APPLY preserves the outer row if only the aggregate matters. Choosing between the two is a semantic call, not a syntactic one.
• COALESCE(SUM(...), 0) inside the aggregate APPLY — SUM over an empty set is NULL. Wrapping in COALESCE yields a proper zero, so downstream reports don't need to handle NULL revenues.
• Two APPLY blocks compose cleanly — each block runs once per outer row with the outer's p.id bound in. Adding a third computation (say, "average order size per product") adds a third block with no side effects on the first two.
• Cost — O(N_p × (P_top3 + P_agg)) where P_top3 is the cost of the top-3 seek (with an index on (product_id, revenue DESC) it's O(log N_o + 3)) and P_agg is the cost of the aggregate scan (with an index on (product_id) it's O(matching_rows)). Both indexes present, this is fast — dramatically better than doing two separate correlated scans in the SELECT list, and beats a ROW_NUMBER() OVER + WHERE + GROUP BY alternative on skewed data.

SQL
Topic — joins (SQL) — hard
Hard SQL joins

Practice →

SQL Topic — subquery Subquery mastery

Practice →

---

4. Top-N per group pattern

sql top-n per group — the LATERAL + LIMIT recipe versus ROW_NUMBER() OVER, and the index shape that makes it fly

The mental model in one line: top-N-per-group is the archetype LATERAL query, and it beats the classic ROW_NUMBER() OVER (PARTITION BY group_col ORDER BY order_col DESC) WHERE rn ≤ N plan whenever the (group_col, order_col DESC) index exists and N is small — because the LATERAL plan is a nested loop with a per-group LIMIT, and the window function is a full sort. Once you know the two shapes and their index requirements, "give me the latest K events per entity" becomes a mechanical choice, not an art.

Three canonical approaches to top-N-per-group.

• LATERAL + LIMIT — FROM outer CROSS JOIN LATERAL (SELECT ... FROM inner WHERE inner.k = outer.k ORDER BY inner.ts DESC LIMIT N) x. Plan is nested loop with a bounded inner. Best when (outer.k, inner.ts DESC) is indexed and N is small.
• ROW_NUMBER() + filter — WITH r AS (SELECT ..., ROW_NUMBER() OVER (PARTITION BY k ORDER BY ts DESC) AS rn FROM inner) SELECT * FROM r WHERE rn <= N. Plan sorts every row, ranks, then filters. Best when index is missing or N is close to inner_rows / outer_rows.
• Correlated scalar subquery per column — SELECT (SELECT MAX(x) FROM inner WHERE inner.k = outer.k) FROM outer. Only for single-column-single-row lookups. Cannot express "top 3."

Why LATERAL wins with the right index.

• With an index on (customer_id, order_ts DESC), the inner query is a B-tree seek to the customer_id + a bounded scan of the next N rows. Cost per outer row is O(log N_inner + N).
• The window function has no way to stop early — it must sort all rows in the inner table (or all rows within each partition, which is asymptotically the same) before applying the filter. Cost is O(N_inner log N_inner).
• On a table with 10M orders and 100K customers, LATERAL + LIMIT 3 is roughly 100K × (log 10M + 3) ≈ 100K × 27 ≈ 2.7M ops. The window function is 10M × log 10M ≈ 10M × 23 ≈ 230M ops. Two orders of magnitude difference — real, reproducible, unmistakable.

When ROW_NUMBER() wins.

• No index available. If you can't add (group_col, order_col DESC), LATERAL loses — every inner query becomes a full sequential scan.
• N is close to inner_rows / outer_rows. If you're asking for "top 100 out of an average 105," you may as well sort everything anyway.
• Cross-DB portability with legacy engines. MySQL 5.7 lacks LATERAL; ROW_NUMBER() is the portable answer.
• Multi-window computations in the same pass. If you also need RANK() OVER and PERCENT_RANK() OVER in the same query, ROW_NUMBER() piggybacks on the existing sort.

The DISTINCT ON shortcut (Postgres only).

• Postgres has SELECT DISTINCT ON (customer_id) * FROM orders ORDER BY customer_id, order_ts DESC — returns exactly one row per customer_id, the first one in the sort order.
• Great for top-1 per group. Doesn't extend to top-N (you'd need N passes).
• LATERAL is more general and reads more clearly across the top-1 → top-N spectrum.

The QUALIFY shortcut (Snowflake / BigQuery / Databricks).

• SELECT * FROM orders QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_ts DESC) <= 3.
• Same plan as WITH ... WHERE rn <= 3, but reads in one clause.
• Portable across Snowflake / BigQuery / Databricks / Teradata; not available on Postgres / MySQL / SQL Server / Oracle.

Skewed-distribution matters — the "hot key" problem.

• If 90% of orders belong to 10% of customers, ROW_NUMBER() OVER still has to sort all 100% of orders. LATERAL runs its bounded scan only against each customer's slice — the 10% of hot customers get slightly more expensive scans, but the 90% of cold customers get near-zero cost.
• On heavy-tailed data (which is 99% of real production workloads), LATERAL's per-group cost adapts naturally.

BigQuery — no LATERAL keyword, but the pattern exists.

• BigQuery uses ARRAY_AGG(... ORDER BY ... LIMIT N) inside a subquery — a materialisation-heavy alternative.
• Or the QUALIFY shortcut with ROW_NUMBER().
• Neither is as clean as ANSI LATERAL, but the semantics are the same.

Ties — a subtle correctness issue.

• ORDER BY order_ts DESC LIMIT 3 returns exactly 3 rows even when there are ties on order_ts. The choice among tied rows is non-deterministic.
• ROW_NUMBER() OVER (…) <= 3 behaves identically — non-deterministic tie-breaking.
• For deterministic top-N with ties, use RANK() <= 3 (returns all tied rows) or add a tie-breaker column to the ORDER BY.

Benchmark shape — the shape every senior candidate should know.

• Setup: 10M orders, 100K customers, index on (customer_id, order_ts DESC), K = 3.
• LATERAL + LIMIT 3: ≈ 1.2s. Plan: nested loop join + index seek + LIMIT.
• ROW_NUMBER() + WHERE rn ≤ 3: ≈ 4.8s. Plan: index scan + sort + filter.
• Correlated scalar per column: ≈ 9.5s (three passes). Plan: N × subquery.
• Ratio: LATERAL is 4× faster than window function, 8× faster than correlated scalar.

Worked example — latest 3 orders per customer with LATERAL

Detailed explanation. The archetype from an interview lens. Given the standard schema and the standard index, write the LATERAL query and explain the plan.

Question. Given orders(id, customer_id, order_ts, total) with an index on (customer_id, order_ts DESC), write a Postgres query returning the three most recent orders per customer.

Input. (10M orders across 100K customers.)

Code.

SELECT c.id, c.name, o.id AS order_id, o.order_ts, o.total
FROM customers c
CROSS JOIN LATERAL (
  SELECT id, order_ts, total
  FROM orders
  WHERE customer_id = c.id
  ORDER BY order_ts DESC
  LIMIT 3
) o
ORDER BY c.id, o.order_ts DESC;

Step-by-step explanation.

1. Outer scan of customers — one row per customer. Cost is O(N_c).
2. Per customer, an index seek into orders using the (customer_id, order_ts DESC) index. Cost O(log N_o).
3. Fetch the next 3 rows sequentially from the index — the ORDER BY is satisfied by the index, so no sort is needed. Cost O(3).
4. LATERAL emits three rows per customer (or fewer, for customers with fewer than three orders). Total inner cost O(N_c × (log N_o + 3)).
5. Final outer ORDER BY is a small sort over ~3 × N_c rows — cheap.

Output. (First few rows.)

id	name	order_id	order_ts	total
1	Alice	104	2026-07-09	60
1	Alice	103	2026-07-08	90
1	Alice	102	2026-07-05	25
2	Bob	106	2026-07-07	30
2	Bob	105	2026-07-02	70

Rule of thumb. LATERAL + LIMIT 3 + covering index is the canonical top-N-per-group plan on Postgres, MySQL 8, and Oracle. When (partition_col, order_col DESC) is indexed, this is dramatically faster than any window-function alternative.

Worked example — latest 3 orders per customer with ROW_NUMBER()

Detailed explanation. The portable alternative. Works everywhere but has to sort every row.

Question. Rewrite the top-3 query using ROW_NUMBER() OVER without LATERAL.

Input. (Same as above.)

Code.

WITH ranked AS (
  SELECT
    o.*,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_ts DESC) AS rn
  FROM orders o
)
SELECT c.id, c.name, r.id AS order_id, r.order_ts, r.total
FROM customers c
JOIN ranked r ON r.customer_id = c.id AND r.rn <= 3
ORDER BY c.id, r.order_ts DESC;

Step-by-step explanation.

1. The CTE ranked computes ROW_NUMBER() over the whole orders table, partitioned by customer_id and ordered by order_ts DESC.
2. The window function requires a sort on the (customer_id, order_ts DESC) composite key. If the index covers both columns, the planner may use an index scan without an explicit sort — but not all planners do.
3. WHERE r.rn <= 3 filters to the top 3 per customer. This happens after the window function has ranked every row.
4. The outer JOIN to customers re-attaches the customer name.
5. Cost O(N_o log N_o) for the sort, plus O(N_o) for the ranking pass. On 10M orders, this is roughly 10M × 23 ≈ 230M ops — 2 orders of magnitude worse than the LATERAL plan.

Output. (Same rows as the LATERAL version.)

Rule of thumb. ROW_NUMBER() OVER (… PARTITION BY g ORDER BY o DESC) <= N is portable across every major engine (except MySQL 5.7 which lacks window functions). Use it when LATERAL is unavailable or when N is close to inner_rows / outer_rows.

Worked example — QUALIFY variant on Snowflake / BigQuery

Detailed explanation. The one-clause shortcut on modern cloud warehouses. Reads better than the CTE + WHERE combo.

Question. Rewrite the top-3-per-customer query using QUALIFY.

Input. (Same as above.)

Code.

-- Snowflake / BigQuery / Databricks / Teradata
SELECT c.id, c.name, o.id AS order_id, o.order_ts, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id
QUALIFY ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.order_ts DESC) <= 3
ORDER BY c.id, o.order_ts DESC;

Step-by-step explanation.

1. QUALIFY filters on window-function results — a shorthand for the CTE + WHERE combo.
2. Semantically identical to the ROW_NUMBER() + WHERE plan — same sort, same filter, same output.
3. Snowflake, BigQuery, Databricks, and Teradata support QUALIFY; Postgres, MySQL, SQL Server, and Oracle do not.
4. On engines that support both, QUALIFY reads better; on engines that support only one, LATERAL wins on perf.

Output. (Same rows.)

Rule of thumb. QUALIFY is syntactic sugar for the CTE + WHERE pattern. It doesn't change the plan, but it does reduce the query from three CTEs to one — a real readability win on complex reports.

Senior interview question on top-N-per-group perf

A senior interviewer might ask: "Design the query to fetch the latest 3 orders per customer on Postgres. Explain when you'd reach for LATERAL, when for ROW_NUMBER() OVER, and what index you'd add to make the LATERAL plan sing."

Solution Using CROSS JOIN LATERAL with the covering index

-- Index (usually already exists on production DBs)
CREATE INDEX IF NOT EXISTS idx_orders_customer_ts_desc
  ON orders (customer_id, order_ts DESC);

-- Query
SELECT
  c.id                                     AS customer_id,
  c.name,
  o.id                                     AS order_id,
  o.order_ts,
  o.total,
  ROW_NUMBER() OVER
    (PARTITION BY c.id ORDER BY o.order_ts DESC) AS rn_per_customer
FROM customers c
CROSS JOIN LATERAL (
  SELECT id, order_ts, total
  FROM orders
  WHERE customer_id = c.id
  ORDER BY order_ts DESC
  LIMIT 3
) o
ORDER BY c.id, o.order_ts DESC;

Step-by-step trace.

Outer (customer_id)	Inner scan	Rows emitted	rn_per_customer
c1 = Alice	seek + 3 rows	(o104, 07-09, 60), (o103, 07-08, 90), (o102, 07-05, 25)	1, 2, 3
c2 = Bob	seek + 2 rows	(o106, 07-07, 30), (o105, 07-02, 70)	1, 2
c3 = Carol	seek + 0 rows	(dropped — CROSS JOIN LATERAL)	—

The outer scans customers once. Per customer, the inner does one index seek + one bounded scan of up to 3 rows. The optional ROW_NUMBER() OVER (PARTITION BY c.id …) adds a within-per-customer ordering label — useful for downstream reports that want "1st most recent," "2nd most recent," "3rd most recent."

Output:

customer_id	name	order_id	order_ts	total	rn_per_customer
1	Alice	104	2026-07-09	60	1
1	Alice	103	2026-07-08	90	2
1	Alice	102	2026-07-05	25	3
2	Bob	106	2026-07-07	30	1
2	Bob	105	2026-07-02	70	2

Why this works — concept by concept:

• Covering index on (customer_id, order_ts DESC) — the LATERAL plan can seek the index to the exact customer, then walk forward for up to three rows in the correct order. No sort, no filter, no rescanning the table. This is the single most important perf lever for LATERAL top-N.
• LIMIT 3 inside the LATERAL is a per-outer-row bound — the inner scan stops after emitting three rows. Compared to WHERE rn <= 3, which needs the window function to rank every row before filtering, the LATERAL plan pays exactly 3 inner-row reads per customer.
• CROSS JOIN LATERAL for the top-N semantics — customers with zero orders should not appear in the report (they have no "latest 3"). CROSS drops them; swap to LEFT JOIN LATERAL ... ON TRUE if the report demands "customers with no orders shown as zeros."
• ROW_NUMBER() OVER runs over the LATERAL output, not the base table — because the LATERAL already limited to 3 rows per customer, the window function operates on at most 3 × N_customers rows. Free labels, no extra sort.
• Cost — with the covering index, O(N_c × (log N_o + K)) where K = 3. On 10M orders × 100K customers, this is ≈ 2.7M ops — sub-second on any modern database. Without the index, the plan degrades to O(N_c × N_o) = O(10^12) — unrun-able. The index is the whole game.

SQL
Topic — top-N per group
Top-N per group drills

Practice →

SQL Topic — window functions Window function library

Practice →

---

5. When LATERAL beats subquery — perf matrix

bigquery lateral, planner behaviour, and LATERAL as an optimizer hint — the six-dialect perf matrix and the rules of thumb

The mental model in one line: LATERAL is worth reaching for whenever the planner would otherwise leave a correlated subquery un-optimised, whenever an index on (group_col, order_col) makes a per-outer LIMIT the winning plan, or whenever set-returning-function unnest requires correlation — and its dialect surface across Postgres, MySQL 8, Oracle 12c+, SQL Server, Snowflake, and BigQuery follows one primitive with six keywords. Once you internalise the four decision axes — plan quality, index availability, unnest requirement, dialect — the LATERAL-vs-subquery choice becomes a checklist.

Four decision axes.

• Plan quality. Does the planner unnest the correlated subquery, or does it leave it as a per-row scan? On modern Postgres 14+, most correlated scalars get unnested to a hash join; on older versions and on some cloud warehouses, unnesting is not guaranteed.
• Index availability. Does an index on (group_col, order_col DESC) exist? If yes, LATERAL + LIMIT is optimal. If no, LATERAL loses to ROW_NUMBER() over a table scan.
• Unnest requirement. Does the inner query need to expand a set-returning function or JSON array? LATERAL is the only clean way to correlate an unnest with an outer row.
• Dialect. Which engine, which keyword? LATERAL / CROSS APPLY / OUTER APPLY / QUALIFY / UNNEST — pick the right one.

Planner behaviour — correlated-scalar unnest failures.

• Postgres 14+ unnests most (SELECT ... FROM inner WHERE inner.k = outer.k) scalar subqueries into a hash join or a merge join. Excellent plan quality.
• Postgres 11–13 unnest correlated scalars less aggressively. Rewrite as LATERAL to force a predictable plan.
• MySQL 8 tries harder than 5.7 but still misses some correlated-scalar cases. LATERAL is the safe rewrite.
• Oracle 12c+ is aggressive about unnesting. LATERAL still helps when you want explicit multi-column output.
• SQL Server 2016+ is competitive. CROSS APPLY is the idiomatic form; the planner recognises the pattern.
• Snowflake and BigQuery are aggressive about scalar unnesting on the query engine side, but LATERAL / UNNEST remain the readable choice.

When LATERAL wins on perf.

• Top-N per group with covering index. The definitive win. LATERAL + LIMIT + index seek is O(N_outer × (log N_inner + K)); window-function alternatives are O(N_inner log N_inner).
• Multi-column top-1 with covering index. Same story — the LATERAL gives you multiple columns from the inner row for one seek.
• JSON / array unnest. No competition — LATERAL is the only clean primitive.
• TVF composition. LATERAL / CROSS APPLY calls the TVF per outer row; the alternative is a SELECT ... FROM outer WHERE outer.k IN (SELECT ...) which flattens the TVF away.
• Correlated aggregates in reporting queries. LATERAL + SUM(...) / COUNT(...) per outer beats correlated scalar subqueries in the SELECT list when the planner isn't unnesting them.

When LATERAL loses (or ties) on perf.

• No index on (group_col, order_col). LATERAL degrades to N × full scan of inner. Window function wins.
• Very small inner tables. With inner_rows < 1000, the planner picks a hash join anyway; LATERAL adds no benefit.
• Cross-warehouse code with legacy engines. MySQL 5.7 lacks LATERAL; QUALIFY / ROW_NUMBER() is the only portable path.
• Very large N. With N close to inner_rows / outer_rows, the per-group LIMIT stops saving reads. Window function ties.
• Skew of 0 (uniform distribution). With inner_rows / outer_rows ≈ constant per group, LATERAL and window function converge on cost.

LATERAL as an optimizer hint.

• Even when a plain correlated subquery would produce the same plan, wrapping it in LATERAL forces the planner into the "nested-loop-with-limit" shape. This is useful when:
  • The default plan is unstable across parameters.
  • You want deterministic worst-case behaviour.
  • The team wants a review-visible signal that per-row semantics are intentional.
• LATERAL is not a hint in the formal sense (like /*+ INDEX(...) */) but functions as one in practice.

Dialect performance matrix — six engines, four patterns.

• Top-N per group. LATERAL / CROSS APPLY / OUTER APPLY / QUALIFY / UNNEST — LATERAL wins on Postgres, MySQL 8, Oracle, SQL Server (as CROSS APPLY), Snowflake (both LATERAL and QUALIFY work); BigQuery uses QUALIFY.
• JSON / array unnest. LATERAL + jsonb_array_elements on Postgres; CROSS APPLY + OPENJSON on SQL Server; LATERAL FLATTEN on Snowflake; JSON_TABLE on Oracle; UNNEST on BigQuery. All engines have a clean primitive.
• TVF compose. CROSS APPLY on SQL Server / Oracle; LATERAL on Postgres / Snowflake / MySQL 8 (with SETOF functions). BigQuery has no user-defined TVF; scalar UDFs only.
• Correlated scalar. All engines support the pattern; unnest quality varies. Postgres 14+ / Snowflake / BigQuery are aggressive; Postgres 11-13 / MySQL 8 less so; LATERAL wraps the pattern for predictability everywhere.

BigQuery specifics — LATERAL by another name.

• BigQuery has no LATERAL keyword.
• UNNEST(array_col) in the FROM clause is implicitly correlated to its parent row. SELECT t.id, x FROM t, UNNEST(t.arr) AS x behaves like CROSS JOIN LATERAL UNNEST(t.arr).
• Scalar-correlation asks are expressed via subqueries — SELECT (SELECT MAX(x) FROM inner WHERE inner.k = outer.k) FROM outer. BigQuery's optimizer is aggressive about unnesting these.
• Top-N per group is QUALIFY + ROW_NUMBER(), not LATERAL + LIMIT.

Snowflake specifics — LATERAL and LATERAL FLATTEN.

• Snowflake ships LATERAL for correlated joins and LATERAL FLATTEN for VARIANT / JSON unnest.
• QUALIFY is available and idiomatic for top-N per group.
• Both LATERAL + LIMIT and QUALIFY + ROW_NUMBER() have similar performance on Snowflake because the columnar engine parallelises the ranking pass.

Rules of thumb, memorised.

• Top-N per group with a covering index? LATERAL + LIMIT.
• Top-N per group with no index? ROW_NUMBER() OVER + WHERE rn ≤ N, or QUALIFY on supporting engines.
• JSON / array unnest? LATERAL (or CROSS APPLY on SQL Server). Every engine has a primitive.
• Reusable per-row logic? CROSS APPLY of an inline TVF on SQL Server; LATERAL over a SETOF function on Postgres.
• Cross-warehouse code? QUALIFY where available; ROW_NUMBER() + WHERE where not.
• Correlated scalar with predictable plan? Wrap in LATERAL for explicit shape.

Worked example — LATERAL + LIMIT vs ROW_NUMBER() OVER — plan shape

Detailed explanation. The interview classic — show the two plans side-by-side and explain the difference.

Question. For orders(id, customer_id, order_ts, total) with an index on (customer_id, order_ts DESC), write both the LATERAL + LIMIT top-3 query and the ROW_NUMBER() + WHERE variant, and describe the difference in plan shape.

Input. (10M orders across 100K customers.)

Code.

-- Plan A: LATERAL + LIMIT
SELECT c.id, o.id, o.order_ts, o.total
FROM customers c
CROSS JOIN LATERAL (
  SELECT id, order_ts, total
  FROM orders
  WHERE customer_id = c.id
  ORDER BY order_ts DESC
  LIMIT 3
) o;

-- Plan B: ROW_NUMBER() + WHERE
SELECT c.id, o.id, o.order_ts, o.total
FROM customers c
JOIN (
  SELECT
    id, customer_id, order_ts, total,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_ts DESC) AS rn
  FROM orders
) o ON o.customer_id = c.id AND o.rn <= 3;

Step-by-step explanation.

1. Plan A — Nested Loop → Index Seek on (customer_id, order_ts DESC) → Limit 3. One seek per customer_id + 3 reads. Cost O(N_c × (log N_o + 3)).
2. Plan B — Sort → Windowed ROW_NUMBER → Filter rn <= 3 → Hash Join to customers. Full sort over all 10M orders + ranking pass. Cost O(N_o log N_o).
3. On the given dataset (10M orders, 100K customers, index present): Plan A ≈ 1.2s wall clock; Plan B ≈ 4.8s wall clock. LATERAL wins by ≈ 4× on this shape.
4. Without the index, Plan A degrades to O(N_c × N_o) — dramatically worse — while Plan B stays at O(N_o log N_o). Adding the index is a hard requirement for LATERAL to beat.
5. When N is large (say, top-1000 per customer): the per-group LIMIT stops saving reads because each customer has fewer than 1000 orders on average, and Plan A degrades to a per-customer full scan; Plan B stays constant. Choose based on N vs inner_rows / outer_rows.

Output. Same rows for both plans; different perf.

Rule of thumb. For small N (top 1–100) with a covering index, LATERAL + LIMIT is the definitive winner. For large N or missing index, window function wins.

Worked example — correlated scalar subquery plan quality

Detailed explanation. A tricky performance case — the same logic as a scalar subquery in the SELECT list. On modern engines, the planner may unnest it; on older engines, it won't. LATERAL forces the good plan.

Question. For each customer, return their name plus the total spend across all their orders. Write both the correlated-scalar version and the LATERAL version, and explain when they diverge on perf.

Input. (Same as above.)

Code.

-- Correlated scalar in SELECT list — plan depends on unnesting
SELECT
  c.id,
  c.name,
  (SELECT COALESCE(SUM(total), 0)
   FROM orders
   WHERE customer_id = c.id) AS total_spend
FROM customers c;

-- LATERAL — deterministic hash-join plan
SELECT
  c.id,
  c.name,
  agg.total_spend
FROM customers c
LEFT JOIN LATERAL (
  SELECT COALESCE(SUM(total), 0) AS total_spend
  FROM orders
  WHERE customer_id = c.id
) agg ON TRUE;

Step-by-step explanation.

1. On Postgres 14+, the correlated scalar version is unnested into a hash-aggregate plan — semantically equivalent to a GROUP BY customer_id join. Fast.
2. On Postgres 11–13, MySQL 8, and some cloud engines, the unnest may not happen. The scalar runs as N × subquery, cost O(N_c × avg_orders_per_customer) — bad on skewed data.
3. The LATERAL version compiles to a nested-loop-with-aggregate or a hash-aggregate join — either way, deterministic. No planner-dependent behaviour.
4. On the same 10M-order dataset with 100K customers: correlated-scalar-unnested ≈ 3s; LATERAL ≈ 3s (same plan); correlated-scalar-un-unnested ≈ 30s.
5. Rule: if you don't trust the planner to unnest, or if you want review-visible determinism, use LATERAL.

Output. (Same rows for both.)

Rule of thumb. LATERAL is a plan-stabiliser for correlated aggregates. When the planner is smart, both versions perform the same; when it's not, LATERAL is the safety net.

Worked example — BigQuery UNNEST as implicit LATERAL

Detailed explanation. BigQuery has no LATERAL keyword but supports the pattern via UNNEST on array columns. The correlation is implicit.

Question. Given orders(id, items ARRAY<STRUCT<sku STRING, qty INT64, price NUMERIC>>) on BigQuery, unroll each item into its own row alongside the parent order id.

Input.

id	items
1	[{sku:"A", qty:2, price:10}, {sku:"B", qty:1, price:25}]
2	[{sku:"C", qty:3, price:5}]

Code.

-- BigQuery: UNNEST is implicit LATERAL
SELECT
  o.id     AS order_id,
  item.sku,
  item.qty,
  item.price
FROM orders o,
     UNNEST(o.items) AS item
ORDER BY o.id, item.sku;

Step-by-step explanation.

1. FROM orders o, UNNEST(o.items) AS item — the comma is a CROSS JOIN; UNNEST(o.items) reads the outer row's items array. This is implicit LATERAL.
2. BigQuery's parser recognises that UNNEST on a column reference must be per-row. No LATERAL keyword needed.
3. To get LEFT-outer semantics — preserve orders with an empty items array — use LEFT JOIN UNNEST(o.items) AS item ON TRUE.
4. UNNEST supports arrays of structs, arrays of primitives, and even repeated fields inside nested structs. The syntax stays the same.
5. Equivalent to SELECT o.id, item.* FROM orders o CROSS JOIN LATERAL UNNEST(o.items) item in ANSI form.

Output.

order_id	sku	qty	price
1	A	2	10
1	B	1	25
2	C	3	5

Rule of thumb. On BigQuery, UNNEST(array_col) in the FROM clause is LATERAL by another name. No keyword, same semantics.

Senior interview question on cross-warehouse portability

A senior interviewer might ask: "Your company runs analytics on Snowflake (batch) and BigQuery (batch). Design the top-3-orders-per-customer query that produces the same output on both warehouses. Where would you use LATERAL, where QUALIFY, and why?"

Solution Using dbt macro with dialect dispatch

-- macros/top_n_orders_per_customer.sql
{% macro top_n_orders_per_customer(customers_rel, orders_rel, n=3) %}
  {%- if target.type == 'snowflake' -%}
    -- Snowflake: LATERAL + LIMIT for index-seek path
    SELECT c.id AS customer_id, c.name, o.id AS order_id, o.order_ts, o.total
    FROM {{ customers_rel }} c
    CROSS JOIN LATERAL (
      SELECT id, order_ts, total
      FROM {{ orders_rel }}
      WHERE customer_id = c.id
      ORDER BY order_ts DESC
      LIMIT {{ n }}
    ) o
  {%- elif target.type == 'bigquery' -%}
    -- BigQuery: QUALIFY + ROW_NUMBER (no LATERAL keyword)
    SELECT c.id AS customer_id, c.name, o.id AS order_id, o.order_ts, o.total
    FROM {{ customers_rel }} c
    JOIN {{ orders_rel }} o ON o.customer_id = c.id
    QUALIFY ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.order_ts DESC) <= {{ n }}
  {%- else -%}
    -- Portable fallback: CTE + WHERE rn <= n
    WITH ranked AS (
      SELECT o.*,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_ts DESC) AS rn
      FROM {{ orders_rel }} o
    )
    SELECT c.id AS customer_id, c.name, r.id AS order_id, r.order_ts, r.total
    FROM {{ customers_rel }} c
    JOIN ranked r ON r.customer_id = c.id AND r.rn <= {{ n }}
  {%- endif -%}
{% endmacro %}

Step-by-step trace.

Dispatch branch	What runs	Why
Snowflake	LATERAL + LIMIT n	Uses per-outer bound for the tightest plan
BigQuery	QUALIFY ROW_NUMBER() OVER ... <= n	No LATERAL keyword on BigQuery
Postgres / MySQL / SQL Server (fallback)	CTE + ROW_NUMBER + WHERE rn <= n	Portable across the remaining engines
All	Same output columns (customer_id, name, order_id, order_ts, total)	Downstream stays warehouse-agnostic

The macro dispatches on target.type at compile time. Snowflake gets the LATERAL variant; BigQuery gets QUALIFY; every other target gets the portable CTE version. All three emit the same output schema.

Output:

customer_id	name	order_id	order_ts	total
1	Alice	104	2026-07-09	60
1	Alice	103	2026-07-08	90
1	Alice	102	2026-07-05	25
2	Bob	106	2026-07-07	30
2	Bob	105	2026-07-02	70

Why this works — concept by concept:

• Compile-time dispatch, zero runtime overhead — dbt evaluates the Jinja block at compile time. The emitted SQL is one of three variants; no runtime branching, no reflection, no penalty.
• Snowflake gets LATERAL for the tighter plan — Snowflake supports LATERAL and pushes the LIMIT into the inner scan. With a supporting clustering key on (customer_id, order_ts), the plan is the tightest available.
• BigQuery gets QUALIFY because there is no LATERAL keyword — QUALIFY + ROW_NUMBER() is idiomatic on BigQuery, reads well, and produces the same output shape as the LATERAL variant.
• Portable fallback uses only CTE + WHERE — every warehouse supports window functions (with the exception of MySQL 5.7, which the fallback would need a further branch for). One SQL that runs on Postgres, MySQL 8, SQL Server, Oracle, and Databricks with no changes.
• Cost — Snowflake LATERAL is O(N_c × (log N_o + K)) with a clustering key; BigQuery QUALIFY is O(N_o log N_o) (columnar sort is cheap); portable CTE is O(N_o log N_o). All three run in single-digit seconds on the 10M × 100K dataset on medium warehouse sizes. The macro adds zero runtime cost; the maintainability win is that downstream consumers see identical output columns regardless of which warehouse produced them.

SQL
Topic — joins (SQL) — medium
Medium SQL joins

Practice →

SQL
Topic — SQL
SQL problem library — 450+ DE-focused questions

Practice →

---

Cheat sheet — LATERAL / CROSS APPLY recipe list

• Two-slot skeleton. Slot 1: the join keyword (CROSS JOIN LATERAL / LEFT JOIN LATERAL ... ON TRUE / CROSS APPLY / OUTER APPLY). Slot 2: the inner subquery that references outer columns. Memorise both; every LATERAL answer fills the two slots.
• CROSS JOIN LATERAL — inner-join semantics; drops outer rows with no inner match. Same as CROSS APPLY on SQL Server.
• LEFT JOIN LATERAL ... ON TRUE — left-outer semantics; preserves outer rows with NULLs when the inner returns zero. Same as OUTER APPLY on SQL Server. The ON TRUE is not optional — every LEFT JOIN needs an ON clause.
• Correlation binding rule. Inside a LATERAL block, the outer row's columns are in scope like function parameters. Without LATERAL, the outer scope is invisible inside the subquery.
• Postgres syntax variants. FROM outer, LATERAL (…) x (comma) or FROM outer CROSS JOIN LATERAL (…) x (explicit JOIN). Same plan; explicit JOIN form is preferred for review clarity.
• SQL Server translation. CROSS APPLY (…) = CROSS JOIN LATERAL (…); OUTER APPLY (…) = LEFT JOIN LATERAL (…) ON TRUE; TOP N inside APPLY = LIMIT N inside LATERAL.
• Top-N per group primitive. CROSS JOIN LATERAL (SELECT … WHERE inner.k = outer.k ORDER BY inner.ts DESC LIMIT N) x. With an index on (k, ts DESC), the plan is nested loop + index seek + LIMIT — dramatically faster than ROW_NUMBER() OVER + WHERE rn ≤ N on skewed data.
• JSON unnest primitive. Postgres: LATERAL jsonb_array_elements(t.payload). SQL Server: CROSS APPLY OPENJSON(t.payload) WITH (…). Snowflake: LATERAL FLATTEN(input => t.payload). BigQuery: UNNEST(t.array_col) in the FROM clause (implicit LATERAL).
• Array unnest primitive. Postgres: LATERAL unnest(t.arr) x(v). Oracle: TABLE(t.arr) x or CROSS APPLY UNNEST(t.arr). BigQuery: UNNEST(t.arr). All produce one row per (parent, element) pair.
• TVF composition primitive. SQL Server: CROSS APPLY dbo.top_n_orders(c.id, 3) t. Postgres: LATERAL top_n_orders(c.id, 3) t for SETOF-returning functions. Reusable per-row business logic centralised in one function.
• Multi-column top-1 primitive. LEFT JOIN LATERAL (SELECT id, order_ts, total FROM orders WHERE customer_id = c.id ORDER BY order_ts DESC LIMIT 1) o ON TRUE. Beats three separate correlated scalars because it does one inner scan for all columns.
• Per-outer aggregate primitive. LEFT JOIN LATERAL (SELECT COUNT(*) AS cnt, COALESCE(SUM(total), 0) AS spend FROM orders WHERE customer_id = c.id) agg ON TRUE. Beats correlated scalar per aggregate; predictable plan.
• Composable LATERAL blocks. Multiple LATERAL blocks in one FROM clause each run once per outer row. Add a third computation — add a third block; no interaction between them.
• DISTINCT ON shortcut (Postgres only). SELECT DISTINCT ON (customer_id) * FROM orders ORDER BY customer_id, order_ts DESC — top-1 per group in one keyword. Doesn't extend to top-N.
• QUALIFY shortcut (Snowflake / BigQuery / Databricks / Teradata). SELECT * FROM orders QUALIFY ROW_NUMBER() OVER (…) <= N. Reads like WHERE-on-window-function. Same plan as CTE + WHERE.
• When LATERAL beats ROW_NUMBER() OVER. Index on (group_col, order_col DESC) present + small N (top 1–100) + skewed distribution + engine supports LATERAL. All four → LATERAL wins by 2–10×.
• When ROW_NUMBER() OVER beats LATERAL. No supporting index, or large N, or uniform distribution, or MySQL 5.7 (no LATERAL). All four → window function wins or ties.
• Dialect matrix. LATERAL: Postgres 9.3+, MySQL 8.0.14+, Oracle 12c+, Snowflake. CROSS APPLY / OUTER APPLY: SQL Server 2005+, Oracle 12c+. UNNEST (implicit LATERAL): BigQuery. QUALIFY: Snowflake, BigQuery, Databricks, Teradata.
• LATERAL as an optimizer hint. Even when a plain correlated subquery gets unnested, wrapping in LATERAL forces the "nested-loop-with-limit" plan — deterministic worst case, review-visible per-row intent.
• Cost model. LATERAL + LIMIT with covering index: O(N_outer × (log N_inner + K)). ROW_NUMBER() OVER: O(N_inner log N_inner). Correlated scalar: O(N_outer × per-outer-cost) with unnesting; O(N_outer × N_inner) without.

Frequently asked questions

What is a SQL LATERAL join and when do you use it?

A sql lateral join is a FROM-clause subquery that runs once per row of the outer relation, with the inner query allowed to reference the outer row's columns like function parameters. Reach for it whenever you need "for each outer row, run this correlated query" — top-N per group with LIMIT (the classic latest-3-orders-per-customer), JSON / array unnest with jsonb_array_elements or OPENJSON or UNNEST correlated to the parent, table-valued-function composition where the TVF takes outer-row arguments, or multi-column top-1 lookups where a scalar subquery would need three separate correlated queries. LATERAL is a keyword introduced in SQL/1999 and shipped in Postgres 9.3 (2013), MySQL 8.0.14 (2019), Oracle 12c (2013), and Snowflake; SQL Server never adopted the keyword but offers CROSS APPLY (inner semantics) and OUTER APPLY (left-outer semantics) with identical behaviour since SQL Server 2005. BigQuery uses implicit correlation via UNNEST on array columns in the FROM clause. Every LATERAL query has a two-slot shape — the join keyword (CROSS JOIN LATERAL vs LEFT JOIN LATERAL … ON TRUE) plus the inner subquery — and once you internalise "for each outer row, run this inner query," the whole family reads like a single primitive across every dialect.

What is the difference between LATERAL and a subquery?

An ordinary subquery in the FROM clause is evaluated independently of the outer query — its scope is only the tables it names, and it cannot see outer columns. A LATERAL subquery is evaluated per outer row with the outer's columns bound in scope, so the inner query can reference outer.k, outer.ts, or any outer column freely. This is the correlation binding rule — LATERAL is the keyword that authorises the outer-column reference. A correlated scalar subquery in the SELECT list (SELECT (SELECT MAX(x) FROM inner WHERE k = outer.k) FROM outer) has a similar per-row behaviour but is restricted to one column, one row — LATERAL generalises that to a whole inner table with multiple columns and multiple rows. Perf-wise, modern planners often unnest correlated scalars into hash joins, but the unnesting is not guaranteed; LATERAL forces a predictable "nested-loop-with-limit" plan that is often faster on skewed data with a supporting index. In short — subqueries are for isolated computations, LATERAL is for correlated ones that need outer-column visibility and typically emit more than one column or more than one row per outer.

How does CROSS APPLY compare to LATERAL?

cross apply sql server is SQL Server's spelling of ANSI LATERAL — same semantics, different keyword. CROSS APPLY (…) maps directly to CROSS JOIN LATERAL (…); outer apply maps to LEFT JOIN LATERAL (…) ON TRUE. The only syntactic differences are the keyword itself and SQL Server's use of TOP N instead of LIMIT N inside the inner query. Every ANSI LATERAL recipe — top-N per group, JSON unnest via OPENJSON, TVF composition, multi-column top-1 — ports to CROSS APPLY with two keyword swaps. Oracle 12c+ ships both keywords (LATERAL and CROSS APPLY / OUTER APPLY), so Oracle teams can pick whichever their style guide prefers. SQL Server has offered CROSS APPLY / OUTER APPLY since 2005, years before Postgres or MySQL added LATERAL, so the APPLY family is deeply embedded in SQL Server codebases and shows up in every "top-N per group" or "TVF-per-row" pattern. When porting Postgres code to SQL Server (or vice versa), the mechanical rules are: CROSS JOIN LATERAL ↔ CROSS APPLY, LEFT JOIN LATERAL … ON TRUE ↔ OUTER APPLY, LIMIT N ↔ TOP N, jsonb_array_elements ↔ OPENJSON. Same primitive; same performance story; different spelling.

How do you get top-N per group with LATERAL?

The archetype: SELECT c.*, o.* FROM customers c CROSS JOIN LATERAL (SELECT * FROM orders WHERE customer_id = c.id ORDER BY order_ts DESC LIMIT N) o. The outer scans customers once; per outer row, the inner runs an index seek on the (customer_id, order_ts DESC) composite index and reads at most N rows. On SQL Server the same query is CROSS APPLY (SELECT TOP N * FROM orders WHERE customer_id = c.id ORDER BY order_ts DESC) o. To preserve customers with no orders, swap CROSS JOIN LATERAL for LEFT JOIN LATERAL … ON TRUE (or CROSS APPLY for OUTER APPLY on SQL Server). This plan is O(N_customers × (log N_orders + K)) — dramatically faster than ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_ts DESC) WHERE rn ≤ N, which is O(N_orders log N_orders) for the sort. On a 10M-order, 100K-customer table with the covering index, LATERAL + LIMIT 3 runs in about 1.2 seconds; the window-function variant runs in about 4.8 seconds — a 4× win. On engines without LATERAL (MySQL 5.7, older SQL Server), fall back to ROW_NUMBER() OVER + WHERE. On Snowflake, BigQuery, Databricks, and Teradata, QUALIFY ROW_NUMBER() OVER (…) <= N is a one-clause shortcut with the same plan as CTE + WHERE.

Which SQL engines support LATERAL / CROSS APPLY in 2026?

As of 2026: Postgres 9.3+ ships full ANSI LATERAL and both CROSS JOIN LATERAL and LEFT JOIN LATERAL ... ON TRUE. MySQL 8.0.14+ ships the same (MySQL 5.7 lacks LATERAL — use ROW_NUMBER() fallback). Oracle 12c+ ships both LATERAL (ANSI) and CROSS APPLY / OUTER APPLY (SQL-Server-compatible). SQL Server 2005+ ships CROSS APPLY and OUTER APPLY with identical semantics to LATERAL. Snowflake ships LATERAL and LATERAL FLATTEN (for VARIANT / JSON unnest). BigQuery has no LATERAL keyword but supports the pattern via implicit correlation on UNNEST(array_col) in the FROM clause; scalar correlation uses subqueries with aggressive planner unnesting. Databricks SQL supports LATERAL VIEW (a Hive-style syntax) plus QUALIFY. Redshift has no LATERAL; use ROW_NUMBER() + WHERE. Streaming SQL engines like Flink SQL and RisingWave inherit LATERAL semantics from ANSI. Memorise the six-row engine × keyword matrix — when an interviewer asks "would you use LATERAL here?" the first question back is always "what's the warehouse?"

Does BigQuery support LATERAL joins?

bigquery lateral is a common question — the short answer is "not by that name, but the semantics are there." BigQuery does not ship the LATERAL keyword. Instead, UNNEST(array_col) in the FROM clause is implicitly correlated to its parent row — SELECT o.id, item.sku FROM orders o, UNNEST(o.items) AS item behaves exactly like CROSS JOIN LATERAL UNNEST(o.items) in ANSI SQL. To preserve rows with an empty array, use LEFT JOIN UNNEST(o.items) AS item ON TRUE — this is BigQuery's LEFT JOIN LATERAL … ON TRUE equivalent. For scalar correlations ("for each row, compute a value from a related table"), BigQuery uses subqueries in the SELECT list; the planner is aggressive about unnesting correlated scalars into hash joins, so perf is competitive with ANSI LATERAL. For top-N per group, BigQuery uses QUALIFY ROW_NUMBER() OVER (PARTITION BY … ORDER BY … DESC) <= N — a one-clause shorthand for the CTE + WHERE pattern. There is no direct BigQuery equivalent to CROSS JOIN LATERAL (SELECT … LIMIT N) with an inner LIMIT — the top-N pattern goes through QUALIFY. When porting a Postgres LATERAL query to BigQuery, the mechanical rules are: LATERAL jsonb_array_elements(…) → UNNEST(…); LATERAL (SELECT … LIMIT N) for top-N → JOIN … QUALIFY ROW_NUMBER() OVER (…) <= N; scalar correlations stay as SELECT-list subqueries and trust the planner.

Practice on PipeCode

• Drill the SQL joins practice library → for CROSS JOIN LATERAL, LEFT JOIN LATERAL ... ON TRUE, CROSS APPLY, and OUTER APPLY variants across Postgres, MySQL 8, Oracle, and SQL Server dialects.
• Rehearse on top-N per group problems → — the archetype LATERAL win where a covering index on (group_col, order_col DESC) beats every ROW_NUMBER() OVER + WHERE rn ≤ N alternative.
• Sharpen the subqueries drill room → to feel the difference between correlated scalar subqueries and LATERAL joins in the FROM clause.
• Push the difficulty ceiling with hard SQL joins → for the composite APPLY + TVF patterns senior interviewers love — multi-column top-1, per-outer aggregate, JSON unnest with correlation.
• Warm up with medium SQL joins → — the mid-difficulty rung where LATERAL vs ROW_NUMBER() OVER trade-offs show up.
• Layer window function drills → — the ROW_NUMBER() + QUALIFY axis that pairs with LATERAL for the top-N-per-group family.
• Practise self-join drills → for the "row-to-row within a table" pattern that LATERAL often replaces with a cleaner form.
• Layer subquery drills → to build the mental map of correlated vs uncorrelated subqueries and where LATERAL fits between them.
• Sharpen the general SQL surface with the SQL practice library → which contains 450+ DE-focused questions covering LATERAL, APPLY, top-N per group, JSON unnest, and every adjacent pattern.
• For the broader SQL interview surface, take the SQL for Data Engineering course →.

Pipecode.ai is Leetcode for Data Engineering — every `sql lateral join` recipe above ships with hands-on practice rooms where you write the two-slot skeleton, wire `CROSS JOIN LATERAL` for top-3-per-customer with a covering index, translate to `cross apply sql server` and `outer apply` for the Microsoft stack, port the same pattern to `postgres lateral`, chase the `lateral vs subquery` performance question with real ANALYZE plans, rehearse the `sql top-n per group` interview classic, compose inline TVFs on SQL Server for reusable per-row logic, unnest JSON with `OPENJSON` and `jsonb_array_elements`, and cross-warehouse-dispatch the whole family to `bigquery lateral` via `UNNEST` and QUALIFY. PipeCode pairs every reading with 450+ DE-focused problems and a real-time scoring engine, so you never have to wonder whether your LATERAL answer holds up under a senior interviewer's depth probes.

Practice SQL joins now →
Top-N per group drills →