What is a LATERAL join?

Q: What is a LATERAL join?

**Target Audience:** Developers comfortable with joins and subqueries who meet `LATERAL` in PostgreSQL code; candidates who need the precise definition and its uses. **Difficulty:** Hard **Engines:** PostgreSQL 16.15 (all LATERAL examples were run there), SQLite 3.45.0 via sql.js 1.10.2 for the runnable SQLite equivalent. MySQL and SQL Server facts are cited, not run. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result, plan and error message below was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First You run a small shop and keep two binders: one of customers, one of orders. Your manager asks for *"each customer with their latest order"*. You could photocopy the whole order binder, sort it, and then try to match pages back to customers. But what you would naturall

The problem

Question presented to candidate: "What does the LATERAL keyword do in a FROM clause? Show me a query that needs it, and tell me how it differs from a normal join to a subquery."

What a strong answer should cover:

A normal derived table in FROM cannot reference other tables of the same FROM clause; LATERAL lets it reference items to its left
Semantics: the lateral subquery is evaluated once per row of the left side, like a correlated subquery that can return many rows and many columns
CROSS JOIN LATERAL drops left rows with no result; LEFT JOIN LATERAL ... ON true keeps them with NULLs
Typical uses: top-N / latest-row per group with ORDER BY ... LIMIT, several columns from one lookup, reusing a computed expression, set-returning functions (implicitly lateral)
Plans: usually a Nested Loop that runs the inner side once per outer row, fast when an index serves the inner lookup
Engine support: PostgreSQL and MySQL 8.0.14+ spell it LATERAL, SQL Server uses CROSS APPLY / OUTER APPLY, SQLite has no LATERAL

Code / implementation expected: Yes — a LATERAL subquery that returns each customer's latest order, and the LEFT JOIN LATERAL variant.

Schema used by the examples:

lateral

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Developers comfortable with joins and subqueries who meet LATERAL in PostgreSQL code; candidates who need the precise definition and its uses. Difficulty: Hard Engines: PostgreSQL 16.15 (all LATERAL examples were run there), SQLite 3.45.0 via sql.js 1.10.2 for the runnable SQLite equivalent. MySQL and SQL Server facts are cited, not run.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result, plan and error message below was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

You run a small shop and keep two binders: one of customers, one of orders. Your manager asks for "each customer with their latest order".

You could photocopy the who

The solution is hidden so you can try first

13 min read · 11 sections

Run the code

Edit the query and run it against the tables for this question.

CROSS JOIN LATERAL: each customer with their most recent order

SELECT c.name, o.id AS order_id, o.ordered_at, o.amount
FROM customers AS c
CROSS JOIN LATERAL (
  SELECT id, ordered_at, amount
  FROM orders
  WHERE orders.customer_id = c.id      -- refers to c, defined to the left
  ORDER BY ordered_at DESC
  LIMIT 1
) AS o
ORDER BY c.name;

LEFT JOIN LATERAL ... ON true keeps customers without orders

SELECT c.name, o.order_id, o.ordered_at, o.amount
FROM customers AS c
LEFT JOIN LATERAL (
  SELECT id AS order_id, ordered_at, amount
  FROM orders
  WHERE orders.customer_id = c.id
  ORDER BY ordered_at DESC
  LIMIT 1
) AS o ON true
ORDER BY c.name;

The plan: a Nested Loop that runs the inner Limit once per customer

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT c.name, o.amount
FROM customers AS c
CROSS JOIN LATERAL (
  SELECT amount FROM orders
  WHERE orders.customer_id = c.id
  ORDER BY ordered_at DESC
  LIMIT 1
) AS o;