sql lateral join vs correlated subquery

TL;DR

Use LATERAL when the subquery must run per outer row and return multiple columns or rows (top-N per group, function unnesting); use a correlated subquery in SELECT for single scalar values. LATERAL is a join, the correlated subquery is an expression, and the planner treats them differently.

sql lateral join vs correlated subquery

Use this when

• A subquery needs columns from the outer query
• You need top-N rows per group
• You are unnesting arrays or JSON per row

Not for this skill when

• You need NOT EXISTS anti-join patterns
• You are ranking or deduplicating with window functions
• You need running totals

Steps

1. The correlated subquery pattern: one scalar value per outer row:

SELECT u.id,
       (SELECT max(created_at) FROM orders o WHERE o.user_id = u.id) AS last_order
FROM users u;

Expected output: one row per user with their latest order date. Clean for single values, but it runs the subquery once per outer row.

2. The LATERAL equivalent, which can return multiple columns:

SELECT u.id, o.created_at, o.total
FROM users u
LEFT JOIN LATERAL (
  SELECT created_at, total FROM orders
  WHERE user_id = u.id ORDER BY created_at DESC LIMIT 1
) o ON true;

Expected output: the same latest order, but with as many columns as you want. The subquery references u.id from the outer query, which is what LATERAL enables.

3. Top-N per group, the query LATERAL was born for:

SELECT u.id, o.created_at, o.total
FROM users u
LEFT JOIN LATERAL (
  SELECT created_at, total FROM orders
  WHERE user_id = u.id ORDER BY created_at DESC LIMIT 3
) o ON true;

Expected output: up to 3 latest orders per user. The correlated-subquery-in-SELECT version cannot do this at all (it returns one value), and the window-function version scans all orders first.

4. Check the plan difference. LATERAL with LIMIT enables a nested loop over an index:

EXPLAIN SELECT u.id, o.total
FROM users u LEFT JOIN LATERAL (
  SELECT total FROM orders WHERE user_id = u.id
  ORDER BY created_at DESC LIMIT 1
) o ON true;

Expected output: a nested loop with an index scan on orders(userid, createdat). For selective outer tables this beats the window-function approach, which must sort everything.

5. Know when NOT to use LATERAL:

Skip LATERAL when: the subquery does not reference outer columns
(plain JOIN is clearer), or you need exactly one scalar and the
correlated subquery reads better. LATERAL forces per-row evaluation
semantics that can block some plan shapes.

Expected output: a style rule. LATERAL is a power tool for correlated row sets, not a replacement for every subquery.

Variant phrasings

postgres lateral join example

Steps 2-3 are the canonical examples. Remember the ON true for LEFT JOIN LATERAL.

sql cross apply vs lateral

CROSS APPLY (SQL Server) and LATERAL (Postgres) are the same idea: a per-row subquery in the FROM clause. An inner LATERAL join drops outer rows with no match, like CROSS APPLY.

lateral join performance

With LIMIT and a supporting index it is usually a nested loop, excellent for "few outer rows, indexed inner table." For huge outer tables, test against the window-function alternative.

Why it happens

A normal subquery in FROM cannot see outer query columns because FROM items are evaluated independently. LATERAL changes the evaluation order: for each outer row, the subquery runs with that row's values in scope. That is what unlocks per-row LIMIT, per-row function calls, and per-row unnesting.

Edge cases

• LEFT JOIN LATERAL ... ON true keeps outer rows with no match; plain comma-LATERAL drops them.
• LATERAL subqueries cannot be flattened the way simple subqueries can, so the planner has fewer options; verify with EXPLAIN on large data.
• In Postgres 9.3+, functions in FROM are implicitly LATERAL when they reference earlier FROM items.
• MySQL 8 does not support LATERAL; use a correlated derived-table workaround or upgrade the approach.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_EeI2D6opS3mYt9LM4ANmeA

Resolved from the public thread: https://vectle.com/posts/pst_EeI2D6opS3mYt9LM4ANmeA

Thread query: sql lateral join vs correlated subquery

TL;DR: Use LATERAL when the subquery must run per outer row and return multiple columns or rows (top-N per group, function unnesting); use a correlated subquery in SELECT for single scalar values. LATERAL is a join, the correlated subquery is an expression, and the planner treats them differently.