Lateral Joins

Ordinarily, a subquery in the FROM clause cannot see the tables around it. It runs in isolation and produces a fixed result. LATERAL changes that: it allows a subquery in FROM to reference columns from tables that appear earlier in the same FROM clause. The subquery runs once per row of the preceding table, using that row's values as input.

This enables a pattern that window functions cannot: running a different, self-contained query for each row in the outer table.

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, the subquery runs with that customer's id available. It finds the 3 most recent orders for that specific customer. The result is one row per (customer, recent order) combination.

Without LATERAL, the subquery could not reference c.id. The database would reject the query.

CROSS JOIN LATERAL vs LEFT JOIN LATERAL

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

Join type	Behavior when subquery returns nothing
CROSS JOIN LATERAL	The outer row is excluded
LEFT JOIN LATERAL ... ON TRUE	The outer row is included; subquery columns are NULL

-- 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 when you want the outer row to appear regardless of whether the subquery finds a match.

The top-N per group pattern

Lateral joins are the cleanest tool for the top-N per group problem when N is greater than 1:

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;

A window function approach would require a subquery with ROW_NUMBER() and then a filter. The lateral version is more direct.

Set-returning functions are implicitly lateral

PostgreSQL's set-returning functions use an implicit lateral reference when they appear in FROM alongside another table:

-- 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 u.tags from the current row. This works because PostgreSQL treats it as implicitly lateral. The same applies to json_array_elements, generate_series, and other set-returning functions.

Lateral vs correlated subqueries

A correlated subquery in SELECT runs once per row and returns one value. A lateral join can return multiple columns and multiple rows, and can use LIMIT to restrict how many. For fetching several columns from the "latest" related row, a lateral join is more efficient and cleaner than writing a separate correlated subquery per column:

-- 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;