Lateral Joins in Postgres for Per-Row Subqueries

PostgreSQL's LATERAL keyword solves a problem that trips up a lot of people writing SQL: how do you run a subquery that's actually aware of the row it's attached to? Standard SQL says no, each item in your FROM clause gets evaluated on its own, blind to everything else in the list. LATERAL LATERAL breaks that rule on purpose, and once you see what it unlocks, "for each row, run this query" is the obvious tool, not merely a workaround.

LATERAL is built to remove the restriction where, in a normal FROM clause, every table or subquery is independent. In a normal FROM clause, every table or subquery is independent. A subquery sitting in your FROM list can't reach over and reference a column from another table in that same list. So if you need correlated logic (something that depends on the current row) you're stuck putting it in SELECT or WHERE, where it can only return one column and one row. That's fine for "what's this customer's total order count." It's not fine for "give me this customer's three most recent orders."

The word itself is a clue. "Lateral" comes from the Latin lateralis, meaning "of the side." A regular subquery looks downward, nested inside its own little bubble. A lateral subquery looks sideways, to the left, at whatever came before it in the FROM clause. That's the whole concept in one sentence.

What LATERAL does when PostgreSQL evaluates a query

Think of LATERAL as a correlated subquery that got promoted from SELECT to FROM, and picked up two new powers on the way: it can return multiple columns, and it can return multiple rows.

Without LATERAL, PostgreSQL will stop you cold:

SELECT * FROM customers c, (SELECT * FROM orders WHERE customer_id = c.customer_id) o;

That throws an error: invalid reference to FROM-clause entry for table "c". PostgreSQL is telling you exactly what the restriction is: the subquery in FROM isn't allowed to see c, because by the standard rules, it was never supposed to.

Adding the keyword makes the exact same reference legal:

SELECT * FROM customers c, LATERAL (SELECT * FROM orders WHERE customer_id = c.customer_id) o;

What changes under the hood? PostgreSQL now evaluates that subquery once per row of customers. For each customer, it plugs in that customer's customer_id, runs the inner query, and attaches whatever comes back. Same idea as a correlated subquery in SELECT, just no longer boxed into a single value.

CROSS JOIN LATERAL vs. LEFT JOIN LATERAL: the distinction that changes results

Once you're writing LATERAL, you have to pick how it joins back to the outer row, and this choice changes your result set in a way that's easy to miss until it bites you.

CROSS JOIN LATERAL (or the shorthand comma + LATERAL) behaves like an inner join. If the subquery returns zero rows for a given outer row, that outer row disappears from the results entirely.

LEFT JOIN LATERAL … ON true keeps every outer row no matter what. If the subquery comes back empty, you get the outer row's columns with NULLs filling in the rest. It's the same logic as an ordinary LEFT JOIN, just extended to a per-row subquery.

That ON true isn't decoration. The whole point of a LEFT JOIN is normally to match rows via an ON condition, but here the matching already happened inside the subquery, correlated to the outer row. There's nothing left for ON to do, so it needs a condition that's trivially satisfied. ON true is that placeholder.

Picture four customers, one of whom, Dana, has never placed an order. Run CROSS JOIN LATERAL against her order history and Dana vanishes from the output, because the subquery returns nothing for her. Run LEFT JOIN LATERAL … ON true instead, so Dana stays in the result set, with NULL in every order-related column. Same subquery, same data, two very different reports. If your dashboard is supposed to show "all customers," the join type is the whole ballgame.

Top-N: the canonical problem LATERAL solves cleanly

Ask for the most recent order per customer, the top three reviews per product, or the latest log line per server, and you've hit the classic top-N-per-group pattern. Standard joins struggle with this pattern cleanly. Window functions work, but usually mean writing a CTE, applying ROW_NUMBER(), and then filtering on the result in a second pass.

LATERAL handles it directly, because ORDER BY and LIMIT inside the subquery apply per row, not globally:

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;

That LIMIT 1 inside the subquery limits the result per customer to one row: their most recent order. It's limiting the result per customer to one row: their most recent order. With an index on (customer_id, placed_at DESC), PostgreSQL can grab that single latest row for each customer with very little work, no full sort of the whole orders table required.

The window function version does the same job, but with a different shape:

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed_at DESC) AS rn
  FROM orders
)
SELECT * FROM ranked WHERE rn = 1;

Functionally similar output, different execution path. Which one wins depends on your data, and that's worth its own section below.

Three more patterns that LATERAL handles naturally

Top-N gets most of the attention, but three other patterns appear constantly once you know to look for them.

JSON and JSONB array expansion: say each row in purchase_requests has a JSONB array of line items, and you need one output row per item.

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;

The same idea works for turning a JSONB object into rows using jsonb_each_text(), useful when you're pivoting wide key-value data into a long format. Technically, LATERAL is implicit whenever you join a set-returning function like this, PostgreSQL assumes it. Writing it out explicitly doesn't change behavior, but it makes the intent obvious to the next person reading the query.

Set-returning functions with arguments that depend on the row. generate_series, unnest, regexp_split_to_table, any function whose input comes from a column in the row before it, needs LATERAL logic to work correctly.

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

Here, generate_series reads u.max_groups fresh for every row, generating a different number of rows per user. Or expand each subscription into its own daily calendar: generate_series(s.start_date, s.end_date, '1 day'), one series per subscription, built from that subscription's own start and end dates.

Collapsing multiple correlated subqueries into one lateral join. If you've got three scalar subqueries stacked in SELECT, all filtering the same foreign key (say, max_salary, min_salary, and avg_salary for an employee, each computed separately), you're running three separate scans of the same inner table. Fold them into a single LEFT JOIN LATERAL that returns all three columns at once, so PostgreSQL only has to scan that inner table once per outer row. Same result, one pass instead of three, and noticeably cleaner SQL to read later. This also matters when you need aggregations that can't share a single window frame: LATERAL lets each aggregation carry its own independent filter.

Conversion funnels also call for the same approach. A homepage view, followed by a demo interaction, followed by a credit card entry, where each step needs to reference the timestamp from the step before it. Without LATERAL, that requires procedural code or significantly more convoluted SQL. With it, each funnel step is a subquery that looks back at the previous step's output directly. That's genuinely practical for product and marketing teams running analytics straight off a production database, without standing up a separate pipeline first.

LATERAL's execution model, indexing, and failure modes

Under the hood, LATERAL runs as a nested loop: for every row on the outer side, PostgreSQL executes the subquery once. That's not automatically bad news. Nested loops are efficient exactly when the inner side is fast, which is the entire game here.

Running EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) on a lateral query shows a Nested Loop node in the plan. That output tells you actual row counts and buffer hits, not just estimates, which matters when you're trying to figure out whether the inner subquery is doing a cheap index lookup or a expensive full scan, four thousand times in a row.

Compared to a plain correlated subquery, the planner has more room to work with a lateral join. Because PostgreSQL treats it as a genuine join, it can use indexes on the correlated column and batch operations instead of treating each row as a totally isolated event.

That flexibility only pays off if the index is right. For any top-N lateral pattern, the index needs to cover both the correlation column and the ORDER BY column, in the direction you're sorting:

CREATE INDEX idx_salaries_emp_date ON salaries (employee_id, effective_date DESC);

With that index in place, PostgreSQL can jump straight to an employee's latest salary record instead of scanning. Without the index, the picture flips: the inner table gets scanned in full, once per outer row. Worst case, cost scales with outer row count multiplied by inner table size, which is exactly the kind of thing that looks fine in testing on a small table and then falls apart in production once the table has real volume.

When to use LATERAL versus correlated subqueries, window functions, and DISTINCT ON

None of these tools are wrong; they're just built for different shapes of problem. Each one fits a different situation, and it's worth walking through when.

LATERAL vs. a correlated subquery in SELECT. A plain correlated subquery is limited to one column, one row, which makes it clean for a single scalar lookup ("this customer's total spend," full stop). Reach for LATERAL the moment you need more than a scalar, multiple columns, multiple rows, or an outer join. It's also the right move when you find yourself stacking several scalar subqueries that all filter on the same key. Consolidate them into one lateral pass instead.

LATERAL vs. window functions. Window functions are portable across databases and efficient for ranking or running totals across a full partition. If the dataset's small, or the cost of scanning the whole partition is acceptable, a window function in a CTE is often simpler to write and just as fast. LATERAL earns its keep when early termination matters, large groups where you only need a handful of rows per group (LIMIT inside the subquery lets PostgreSQL stop early instead of ranking the whole partition), when the subquery logic is more complex than a simple rank, or when you want several columns back without wiring up extra CTEs.

LATERAL vs. DISTINCT ON. DISTINCT ON is genuinely elegant Postgres shorthand, but it only solves N=1. Need the top three per group instead of the top one? DISTINCT ON can't get you there. LATERAL with LIMIT N handles any N with the same pattern, which also matters for maintainability: DISTINCT ON semantics (which row survives the dedup depends on your ORDER BY) trip up people unfamiliar with it. A LATERAL subquery with an explicit ORDER BY and LIMIT reads more clearly to the next person who opens the file.

One portability note: all of this is specific to a particular database engine. SQLite and MariaDB don't support LATERAL at all, so if the SQL has to run across multiple database engines, window functions or correlated subqueries are the safer bet. SQL Server has its own version, CROSS APPLY and OUTER APPLY, which do roughly the same job with different syntax. If the workload lives entirely on Postgres, though, LATERAL is frequently the clearest way to write the query, and with the right index behind it, often the fastest too.

LATERAL queries in practice: dashboards, BI tools, and self-serve data access

Most of the queries powering dashboards, "latest value" widgets, cohort tables, and funnel charts, come down to exactly the per-row aggregations and top-N lookups LATERAL was built for. The same problem shape appears wherever someone wants "the most recent X for every Y," and that's not a coincidence."

BI tools generate SQL automatically against whatever database sits underneath them. When the generated query includes a LATERAL join, its performance depends entirely on how well that generated query lines up with the indexes actually present on the tables. A tool doesn't know your indexing strategy; it just writes the SQL its logic calls for. That's exactly why data teams and DBAs need visibility into what's actually being generated and run, including the dashboard output on the screen.

That visibility matters more as self-serve analytics keeps growing. Open source BI tools running in production climbed from 28% of organizations in 2022 to 41%, a shift in which a meaningful share means more people querying production databases directly instead of going through a managed warehouse layer first. That's a real change in who's triggering complex SQL. Non-engineers, clicking through a dashboard, unknowingly kick off a LATERAL join running against live data, with zero awareness of whether the right index exists underneath it.

Which loops back to the indexing point from earlier, not as an aside but as the whole stakes of the thing. The gap between "index exists, LATERAL runs a targeted lookup per row" and "no index, LATERAL scans the inner table over and over" is the exact gap between a dashboard that loads instantly and one that spins for whoever happens to be looking at it, expecting an answer in seconds.