LATERAL joins: a subquery per row

Part of the free LogicWiz SQL & Databases course, Module 12: CTEs and recursive queries. The queries and practice run in the app on real Postgres.

The store's home screen shows each customer their two most recent orders. You could rank every order with a window function and filter, but the question really says: for each customer, fetch two orders. Can SQL run a small query once per row?

You'll learn to

• Explain what LATERAL lets a subquery in FROM see
• Write top-N per group with JOIN LATERAL and ORDER BY ... LIMIT
• Keep rows with no match using LEFT JOIN LATERAL ... ON true
• Choose between LATERAL and a window function, and say why in an interview

Learn

A subquery that can see the row on its left

A normal subquery in FROM (a derived table) is blind: it cannot use columns of the other tables in the FROM list. It runs once, on its own.

Put LATERAL in front and it can. For each row on its left, Postgres runs the subquery again with that row's values. Think of it as a for-each loop:

for each customer c:
    run: SELECT ... FROM orders WHERE customer_id = c.customer_id ... LIMIT 2

Because the subquery runs per row, ORDER BY ... LIMIT n inside it means n rows per customer, not n rows in total. That is top-N per group.

Each customer's latest order

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

The subquery uses c.customer_id from the left side, which only LATERAL allows. ON true is there because JOIN needs an ON, and the real matching already happens inside the subquery. 12 customers go in, 10 rows come out: Dana Cohen and Adam Novak have no orders.

Two latest orders, keeping customers with none

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

LEFT JOIN LATERAL ... ON true keeps a customer even when the subquery returns nothing, with NULLs on the right. Victor Petrov has one order, so he gets one row.

What the subquery returns for a few customers (LIMIT 2)

The subquery runs once per customer. LIMIT 2 cuts each customer's list separately.

LATERAL or a window function?

Two classic slips. Forgetting ORDER BY inside the subquery: LIMIT 1 then returns any row, not the latest. Using JOIN when every left row must appear: a customer with no orders silently disappears. Write LEFT JOIN LATERAL (...) alias ON true.

Interviewers ask "top N per group" expecting a window function first. Give it, then add: "On Postgres with an index on (group, sort column) and few groups, I would use LATERAL with ORDER BY ... LIMIT N, because it reads only N rows per group." They also like "latest row per user" and "nearest match per row" (the closest earlier price, the last status before a date). If they use SQL Server, say CROSS APPLY and OUTER APPLY.

In one line

JOIN LATERAL (SELECT ... WHERE x = left.x ORDER BY ... LIMIT n) t ON true runs a subquery per row: top-N per group that reads like the question; use LEFT to keep rows with no match.

Interview corner

What is a LATERAL join?

A join to a subquery that can reference columns of the tables before it in FROM. Postgres evaluates it for each left row, like a for-each loop. SELECT c.name, o.* FROM customers c JOIN LATERAL (SELECT * FROM orders WHERE customer_id = c.customer_id ORDER BY ordered_at DESC LIMIT 3) o ON true; gives the 3 latest orders per customer.

LATERAL vs ROW_NUMBER for top-N per group: which is faster?

It depends on the shape. With few groups, many rows per group and an index on (group, sort column), LATERAL with LIMIT reads only N index entries per group. When you need most rows anyway, or need ties kept with RANK, the window function over one scan is usually better. Say both and pick by the data.

How do you keep left rows when the LATERAL subquery returns nothing?

Use LEFT JOIN LATERAL (...) t ON true. A plain JOIN LATERAL (or a comma with LATERAL) drops those rows, just like an inner join.

What is CROSS APPLY?

SQL Server and Oracle's name for LATERAL. CROSS APPLY is JOIN LATERAL ... ON true, and OUTER APPLY is LEFT JOIN LATERAL ... ON true. MySQL supports LATERAL from 8.0.14.