SQL LATERAL Joins Explained: Correlated Subqueries Made Easy

The Problem LATERAL Solves

In a regular JOIN, the right-hand side cannot reference columns from the left-hand side. Each side is evaluated independently. But sometimes you need the right side to depend on the current row from the left side — for example, “for each customer, find their 3 most recent orders.”

A correlated subquery can do this, but it is limited to returning a single value. LATERAL removes that limitation: it lets a subquery in the FROM clause reference columns from preceding tables, and it can return multiple rows and columns.

Syntax

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

The key is the LATERAL keyword. Without it, the subquery cannot reference c.id. With it, PostgreSQL evaluates the subquery once for each row of customers.

You can also use LEFT JOIN LATERAL ... ON true to keep customers that have no matching orders:

SELECT c.name, recent.id, recent.total
FROM customers c
LEFT JOIN LATERAL (
  SELECT o.id, o.total, o.created_at
  FROM orders o
  WHERE o.customer_id = c.id
  ORDER BY o.created_at DESC
  LIMIT 3
) AS recent ON true;

LATERAL vs Correlated Subqueries

A correlated subquery in the SELECT list can only return one value:

-- Works: single value
SELECT c.name,
       (SELECT MAX(o.total) FROM orders o WHERE o.customer_id = c.id) AS max_total
FROM customers c;

-- Fails: cannot return multiple columns or rows in SELECT

LATERAL has no such restriction:

SELECT c.name, stats.order_count, stats.avg_total, stats.max_total
FROM customers c
CROSS JOIN LATERAL (
  SELECT COUNT(*) AS order_count,
         AVG(total) AS avg_total,
         MAX(total) AS max_total
  FROM orders o
  WHERE o.customer_id = c.id
) AS stats;

Top-N Per Group Pattern

The most common LATERAL use case is fetching the top N rows per group. Compare three approaches:

Approach 1: Window Function

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

This scans and ranks all orders, then discards most of them. For large tables with few groups, this can be slow.

Approach 2: LATERAL Join

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

This fetches only 3 orders per customer using an index on (customer_id, created_at DESC). For a small number of groups with many rows each, LATERAL is significantly faster.

When LATERAL Wins

LATERAL tends to outperform window functions when:

• The number of groups is small relative to the total rows.
• You only need N rows per group (LIMIT inside LATERAL short-circuits early).
• An index on (group_column, sort_column) exists.

Window functions tend to win when:

• The number of groups is large.
• You need to process all rows anyway.

LATERAL with Set-Returning Functions

LATERAL is implicit with set-returning functions like unnest, generate_series, and jsonb_array_elements:

-- Expand an array column into rows
SELECT p.name, tag
FROM products p,
     unnest(p.tags) AS tag;

-- Equivalent explicit LATERAL
SELECT p.name, t.tag
FROM products p
CROSS JOIN LATERAL unnest(p.tags) AS t(tag);

LATERAL for Time-Series Lookups

Find the most recent sensor reading before each event:

SELECT e.event_id, e.event_time, r.value, r.recorded_at
FROM events e
LEFT JOIN LATERAL (
  SELECT sr.value, sr.recorded_at
  FROM sensor_readings sr
  WHERE sr.sensor_id = e.sensor_id
    AND sr.recorded_at <= e.event_time
  ORDER BY sr.recorded_at DESC
  LIMIT 1
) AS r ON true;

This pattern is extremely efficient with an index on (sensor_id, recorded_at DESC).

LATERAL for Running Calculations

Compute a running balance where each row depends on the previous:

SELECT t.id, t.amount, t.txn_date, balance.running
FROM transactions t
CROSS JOIN LATERAL (
  SELECT SUM(t2.amount) AS running
  FROM transactions t2
  WHERE t2.account_id = t.account_id
    AND t2.txn_date <= t.txn_date
) AS balance
WHERE t.account_id = 1
ORDER BY t.txn_date;

For this specific case, a window function (SUM(amount) OVER (ORDER BY txn_date)) is more idiomatic. Use LATERAL when the logic is more complex than a simple aggregate.

Performance Considerations

LATERAL executes the subquery once per outer row. This means:

1. Index the inner query’s filter columns. Without an index, each execution is a sequential scan.
2. Use LIMIT inside LATERAL to stop scanning early.
3. Watch the row count. If the outer table has 1 million rows and the inner subquery is non-trivial, LATERAL will be slow.

-- Essential index for the top-N pattern
CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at DESC);

Check the plan with EXPLAIN ANALYZE to confirm the inner subquery uses an Index Scan rather than a Seq Scan.

Database Support

• PostgreSQL: Full LATERAL support since version 9.3.
• MySQL: Supported since 8.0.14.
• SQL Server: Use CROSS APPLY (equivalent to CROSS JOIN LATERAL) and OUTER APPLY (equivalent to LEFT JOIN LATERAL).
• SQLite: Not supported.

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

Summary

LATERAL joins let a subquery in the FROM clause reference columns from preceding tables, enabling multi-row, multi-column correlated lookups. They excel at top-N-per-group queries when combined with LIMIT and an appropriate index. Use CROSS JOIN LATERAL when every outer row must have matches, and LEFT JOIN LATERAL ... ON true to preserve outer rows with no matches.