What is Postgres LATERAL JOIN?

A LATERAL JOIN lets a subquery in the FROM clause reference columns from tables that appear earlier in the same FROM list. PostgreSQL evaluates that subquery once per row of the left-side table, much like a correlated subquery, except you write it in FROM instead of in WHERE or SELECT.

The plain-language version: use it when you need "for each row X, run this query that depends on X."

Basic Syntax#

The fundamental pattern looks like this:

FROM left_table lt
LEFT JOIN LATERAL (
  SELECT ...
  FROM ...
  WHERE ... lt.col ...
) s ON true

You can also write it as:

FROM lt, LATERAL (SELECT ...) s              -- CROSS JOIN LATERAL
FROM lt CROSS JOIN LATERAL (SELECT ...) s    -- explicit CROSS JOIN

The difference between CROSS JOIN LATERAL and LEFT JOIN LATERAL matters, and it's the one thing people get wrong most often. CROSS JOIN LATERAL drops a left row when the subquery returns nothing for it. LEFT JOIN LATERAL keeps every left row and fills the right side with NULLs when there's no match, exactly like an ordinary LEFT JOIN.

Why is it Called "Lateral"?#

The word comes from Latin lateralis, "of or pertaining to the side." In SQL it refers to the subquery's ability to look sideways (laterally) at columns from tables that appear to its left in the FROM clause.

In the SQL standard, a lateral derived table is a subquery in FROM that can refer to columns from items appearing earlier in FROM. "Lateral" means the dependency runs sideways to the left, not downward like a nested subquery. So LATERAL is really just a signal: this subquery may use columns from the tables before it.

Core Example: Top-N Per Group#

The classic case is finding the top N records for each group. Say you want the most recent order for each customer:

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

The key thing here is that the LIMIT 1 runs per customer, not globally. With an index on (customer_id, placed_at DESC), PostgreSQL can grab the most recent order for each customer with very little work. The usual alternatives, window functions or self-joins, tend to need more involved logic and can run slower.

Common Use Cases#

Expanding JSON arrays per row. When you have JSON stored in a column and need to unfold it into multiple rows:

SELECT p.id,
       item->>'sku' AS sku,
       (item->>'qty')::int AS qty
FROM purchase_requests p
LEFT JOIN LATERAL jsonb_array_elements(p.items_json) AS item ON true;

Each row's items_json array becomes multiple rows, one per element.

Set-returning functions. Functions that return multiple rows pair naturally with LATERAL, though the keyword is optional for functions:

SELECT u.id, g
FROM users u
JOIN generate_series(1, u.max_groups) AS g ON true;

Here generate_series can read u.max_groups from the preceding table.

Conversion funnel analysis. Tracking a user's journey through a sequence of events where each step depends on the one before it. For instance, finding users who viewed a homepage, then used a demo within a week, then entered credit card details within another week. Each LATERAL join is a funnel step that can reference timestamps and conditions from the prior steps.

Performance Considerations#

LATERAL joins run as nested loop joins. For N rows on the left and M rows per subquery, that's O(N×M) in the worst case. With the right indexing, though, it's often very efficient:

Run EXPLAIN (ANALYZE, BUFFERS) to confirm the plan and check that your indexes are actually being used. The query that looks efficient on paper isn't always the one Postgres picks.

Gotchas#

A lateral subquery can only reference items that come before it in FROM. Order matters. If you write FROM a, LATERAL (SELECT ...) s, b, the subquery can see a but not b.

CROSS JOIN LATERAL quietly drops left rows that have no match. If you want to keep every left row, the way a standard LEFT JOIN would, use LEFT JOIN LATERAL ... ON true.

And it's easy to accidentally multiply rows. If you only want one row per left row, make sure the subquery has ORDER BY ... LIMIT 1.

Quick Reference Patterns#

Top N items per parent:

SELECT p.id, i.*
FROM projects p
LEFT JOIN LATERAL (
  SELECT *
  FROM issues i
  WHERE i.project_id = p.id
  ORDER BY i.priority DESC, i.created_at DESC
  LIMIT 3
) i ON true;

Tokenize text per row:

SELECT d.id, t.token
FROM docs d
LEFT JOIN LATERAL regexp_split_to_table(d.body, '\s+') AS t(token) ON true;

Per-row function with parameters:

SELECT a.id, nearby.id AS nearby_id, nearby.distance
FROM addresses a
LEFT JOIN LATERAL (
  SELECT b.id, st_distance(a.geom, b.geom) AS distance
  FROM addresses b
  WHERE b.city = a.city
  ORDER BY a.geom <-> b.geom
  LIMIT 1
) nearby ON true;

Conclusion#

LATERAL joins solve a whole class of problems that would otherwise need window functions, procedural code, or awkward self-joins. They're at their best in top-N-per-group queries, with set-returning functions, and anywhere you need to run a parameterized query for each row of a table. Once it clicks, you'll reach for it more often than you'd expect.