The Power of LATERAL Joins: Turning SQL into a "ForEach" Loop

Most developers exclusively use an ORM — Prisma, Sequelize, Hibernate — which means they may never actually learn how to "loop" in SQL. For 90% of scenarios, that's fine. But when you hit a table with 10M+ rows and your ORM-generated join is spilling to disk, you need to understand what's actually happening underneath.

LATERAL joins are the SQL construct that turns a set-based operation into something that behaves like a forEach loop. Once you understand it, a whole class of slow queries becomes fast.

What Is a LATERAL Join?

In a standard SQL join, the subquery in your FROM clause is evaluated once, independently of the outer query. It cannot reference columns from tables listed before it.

A LATERAL subquery breaks that rule. It's re-evaluated for each row of the preceding table, and it can reference columns from that row. That's the entire mental model:

lateral = per-row subquery execution

sql

SELECT *
FROM outer_table o,
LATERAL (
  SELECT *
  FROM inner_table i
  WHERE i.foreign_key = o.id   -- references the current outer row
  LIMIT 3
) sub;

This is semantically equivalent to a forEach in application code:

javascript

for (const outerRow of outerTable) {
  const results = innerTable
    .filter(r => r.foreign_key === outerRow.id)
    .slice(0, 3);
}

The difference: it happens entirely in the database, in a single query, with the planner able to use indexes on every iteration.

Use Case 1: Top-N Per Group

The most common LATERAL use case — fetch the N most recent rows for each parent record.

Problem: You have a wallets table and a transactions table. You want the 3 most recent transactions for each wallet.

The naive approach with a window function:

sql

-- Works, but scans and ranks all transactions first
SELECT *
FROM (
  SELECT
    w.id AS wallet_id,
    t.*,
    ROW_NUMBER() OVER (
      PARTITION BY t.wallet_id
      ORDER BY t.created_at DESC
    ) AS rn
  FROM wallets w
  JOIN transactions t ON t.wallet_id = w.id
) ranked
WHERE rn <= 3;

This has to read and rank every transaction before filtering. On 20M rows, that's a full table scan.

With LATERAL:

sql

SELECT w.id AS wallet_id, recent.*
FROM wallets w
CROSS JOIN LATERAL (
  SELECT *
  FROM transactions t
  WHERE t.wallet_id = w.id
  ORDER BY t.created_at DESC
  LIMIT 3
) recent;

With a composite index on (wallet_id, created_at DESC), the planner uses a nested loop: for each wallet, it does an index seek to grab exactly 3 rows. No full scan. No ranking pass.

sql

CREATE INDEX idx_txn_wallet_created
  ON transactions (wallet_id, created_at DESC);

On 500K wallets with 20M transactions: the window function version takes 40+ seconds; the LATERAL version takes under 500ms.

Use Case 2: Forcing Nested Loops — 29 Minutes to 0.3ms

This is where LATERAL becomes a surgical tool for query optimization.

The planner makes join strategy decisions based on table statistics. When it gets it wrong — choosing a hash join or merge join on a large table — you can use LATERAL to force the strategy you know is correct.

A real production example from SupraScan: We had a query joining blocks to events to find the latest event of each type for a given contract. The planner chose a merge join, sorting hundreds of thousands of rows.

sql

-- Before: planner chose merge join — 29 minutes
SELECT DISTINCT ON (e.event_type)
  e.*
FROM blocks b
JOIN events e ON e.block_height = b.height
WHERE b.chain_id = $1
  AND e.contract_address = $2
ORDER BY e.event_type, e.created_at DESC;

After — LATERAL forces a targeted index seek per event type:

sql

-- After: nested loop via LATERAL — 0.3ms
SELECT latest.*
FROM (
  SELECT UNNEST(ARRAY['Transfer', 'Mint', 'Burn', 'Approval']) AS event_type
) types
CROSS JOIN LATERAL (
  SELECT e.*
  FROM events e
  WHERE e.contract_address = $2
    AND e.event_type = types.event_type
  ORDER BY e.created_at DESC
  LIMIT 1
) latest;

sql

-- The index that makes each iteration a single seek
CREATE INDEX idx_events_contract_type_created
  ON events (contract_address, event_type, created_at DESC);

With this index, each LATERAL iteration is one index seek. Four event types = four index seeks, each returning exactly one row. The planner cannot choose anything other than nested loops — which is exactly what you want when your outer set is small and your index is selective.

Result: 29 minutes → 0.3 milliseconds.

The key insight is that LATERAL keeps your working set small at each step. Instead of joining two large tables and filtering afterward, you filter first — at the index level — before any join. Memory stays low, disk spills don't happen, and the index works on every iteration.

Use Case 3: Unnesting JSONB and Arrays

LATERAL joins are essential when working with set-returning functions like UNNEST and jsonb_array_elements, because those functions need per-row context from the outer table.

Example — JSONB array expansion:

sql

-- users table with a preferences JSONB column containing an array
-- Expand each preference entry and filter enabled ones

SELECT u.id, u.email, pref.value
FROM users u
CROSS JOIN LATERAL jsonb_array_elements(u.preferences) AS pref(value)
WHERE pref.value->>'enabled' = 'true';

Without LATERAL, you cannot reference u.preferences inside jsonb_array_elements. The implicit comma syntax (, instead of CROSS JOIN LATERAL) works too — PostgreSQL treats it as LATERAL when a set-returning function appears — but explicit LATERAL makes the intent clear.

Example — plain array unnesting with a join:

sql

-- posts table has a tags integer array; join each tag to a tag_definitions table
SELECT p.id, p.title, t.name AS tag_name
FROM posts p
CROSS JOIN LATERAL UNNEST(p.tag_ids) AS tag_id
JOIN tag_definitions t ON t.id = tag_id;

This pattern replaces multiple round-trips or suboptimal ANY(array) queries with a single clean pass.

Use Case 4: Keyset Pagination

Standard OFFSET pagination breaks on large tables — scanning 500K rows to skip the first 499,990 is a full table read disguised as a feature.

LATERAL enables efficient cursor-based pagination:

sql

-- Get the next 20 transactions per wallet after a given cursor
SELECT w.id, page.*
FROM wallets w
CROSS JOIN LATERAL (
  SELECT id, amount, created_at
  FROM transactions t
  WHERE t.wallet_id = w.id
    AND (t.created_at, t.id) < ($last_seen_at::timestamptz, $last_seen_id::bigint)
  ORDER BY t.created_at DESC, t.id DESC
  LIMIT 20
) page
WHERE w.user_id = $user_id;

Page 500 loads in the same time as page 1. The (created_at, id) tuple comparison is the cursor — the index makes each seek O(log n) regardless of which page you're on.

When NOT to Use LATERAL

LATERAL is the right tool when:

Your outer set is small and the inner query is highly selective (with a matching index)
You need top-N per group
You're working with set-returning functions (UNNEST, jsonb_array_elements)
You need to override the planner's join strategy choice

Don't reach for LATERAL when:

You're doing a simple 1:1 join — regular JOIN optimizes better
Your outer set is large and the inner query isn't selective — you'll get N expensive seeks instead of one efficient scan
The planner's chosen strategy is actually correct for your data distribution

Always verify with EXPLAIN (ANALYZE, BUFFERS):

sql

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT w.id, recent.*
FROM wallets w
CROSS JOIN LATERAL (
  SELECT * FROM transactions t
  WHERE t.wallet_id = w.id
  ORDER BY t.created_at DESC
  LIMIT 3
) recent;

Look for Nested Loop + Index Scan in the plan output. If you see Hash Join or Seq Scan, the index is missing or statistics are stale — run ANALYZE transactions and re-check.

Summary

Pattern	When to use
Top-N per group	Small outer set, large inner table, composite index available
Force nested loop	Planner chooses wrong strategy; outer set is small and selective
JSONB / array expansion	Any set-returning function that needs parent row context
Keyset pagination	Replace OFFSET on large tables
Avoid	1:1 joins, large outer sets without selective indexes

A LATERAL join lets a subquery reference the current row of the outer query. Use it when you want the planner to perform N targeted index seeks instead of one large scan — and when you have the index to make each seek fast.

The moment your ORM-generated query starts spilling to disk on a large table, check whether LATERAL can replace it. Drop to raw SQL, write the LATERAL subquery, create the composite index, and run EXPLAIN ANALYZE. The plan will change from Seq Scan → Hash Join → Sort to Nested Loop → Index Scan — and your query time will drop with it.

All examples tested on PostgreSQL 15+. The 29-minute → 0.3ms optimization is from a real production query on SupraScan's blockchain indexer, running on 20–30 TB of indexed blockchain data.

The Power of LATERAL Joins: Turning SQL into a “ForEach” Loop