Lateral Joins

Every subquery you have written in FROM so far was self-contained. It could not see the tables around it. That limitation is fine until you need something like "for each customer, give me their 3 most recent orders" - a query that needs a different result per outer row.

LATERAL removes that limitation. It lets a subquery in FROM reference columns from tables that appear earlier in the same FROM clause. The subquery runs once per outer row, using that row's values as input. The result can have multiple columns and multiple rows, unlike a correlated subquery in SELECT.

The basic syntax

SELECT c.name, recent.order_date, recent.total
FROM customers c
CROSS JOIN LATERAL (
  SELECT order_date, total
  FROM orders
  WHERE orders.customer_id = c.id  -- c.id comes from the outer row
  ORDER BY order_date DESC
  LIMIT 3
) recent;

For each customer row, the subquery runs with that customer's id in scope. Without LATERAL, referencing c.id inside a FROM subquery is an error - the database rejects it. With LATERAL, it works exactly as you would hope.

CROSS JOIN LATERAL vs LEFT JOIN LATERAL

The join type determines what happens when the subquery returns no rows:

-- Customers with no orders are excluded
FROM customers c
CROSS JOIN LATERAL (
  SELECT order_date, total FROM orders WHERE customer_id = c.id LIMIT 1
) latest

-- Customers with no orders appear with NULL order columns
FROM customers c
LEFT JOIN LATERAL (
  SELECT order_date, total FROM orders WHERE customer_id = c.id LIMIT 1
) latest ON TRUE

Use LEFT JOIN LATERAL whenever you want to keep the outer row even if the subquery comes back empty. CROSS JOIN LATERAL drops the row entirely - same behavior as an inner join.

The top-N per group pattern

The top-N per group problem is where lateral joins really shine. You want the top 3 orders per customer - not just the top 1, where a correlated subquery would do. Here is the lateral version:

SELECT c.name, top_orders.order_date, top_orders.total
FROM customers c
LEFT JOIN LATERAL (
  SELECT order_date, total
  FROM orders
  WHERE customer_id = c.id
  ORDER BY total DESC
  LIMIT 3
) top_orders ON TRUE;

The window function alternative requires wrapping the whole thing in a subquery just to filter on ROW_NUMBER(). The lateral version is more direct - the LIMIT lives right where the logic is.

Set-returning functions are implicitly lateral

You have probably written this without knowing it was lateral:

-- Expand each user's tags array into individual rows
SELECT u.id, u.name, tag
FROM users u, unnest(u.tags) AS tag;

The unnest(u.tags) call references a column from the current outer row - that is a lateral reference. PostgreSQL handles it implicitly for set-returning functions like unnest, json_array_elements, and generate_series. No LATERAL keyword needed.

Lateral vs correlated subqueries

The practical difference: a correlated subquery in SELECT returns one value per row. A lateral join can return multiple columns and multiple rows, and it only scans the related table once per outer row. When you need two or three columns from the same "latest" row, lateral wins:

-- Instead of this (two correlated subqueries, two scans):
SELECT s.id,
  (SELECT type FROM events WHERE session_id = s.id ORDER BY created_at DESC LIMIT 1),
  (SELECT created_at FROM events WHERE session_id = s.id ORDER BY created_at DESC LIMIT 1)
FROM sessions s;

-- Use this (one lateral join, one scan per session):
SELECT s.id, e.type, e.created_at
FROM sessions s
LEFT JOIN LATERAL (
  SELECT type, created_at FROM events
  WHERE session_id = s.id
  ORDER BY created_at DESC
  LIMIT 1
) e ON TRUE;

Once you see lateral joins, you will start noticing how often a problem is really just "run this query once per row." That framing - per-row subquery - is the mental model that makes lateral click.