0Pricing
PostgreSQL Performance & Query Optimization · Lesson

Optimizing LATERAL Joins and Correlated Lookups

Learn how LATERAL joins let a subquery reference columns from earlier tables, and how to use them to replace slow correlated subqueries with efficient per-row lookups.

Optimizing LATERAL Joins and Correlated Lookups is a free PostgreSQL Performance & Query Optimization lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the PostgreSQL Performance & Query Optimization learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What is a LATERAL Join?

A LATERAL join lets a subquery in the FROM clause reference columns from tables listed before it. Without LATERAL, a subquery in FROM cannot see the outer rows.

The Problem It Solves

Suppose you want, for each customer, their three most recent orders. A plain join cannot easily limit rows per customer. LATERAL evaluates the subquery once per outer row, so a per-row LIMIT works.

Basic Syntax

Place LATERAL before the subquery and reference the outer table inside it.

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

LEFT JOIN LATERAL

Use LEFT JOIN LATERAL ... ON true when you still want outer rows that have no matching subquery results, such as customers with no orders.

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

LATERAL vs Correlated Subquery

A correlated subquery in the SELECT list can only return one column per row. LATERAL can return multiple columns and multiple rows, making it far more flexible.

Indexing for LATERAL

Because the subquery runs once per outer row, the inner filter and sort must be index-backed. Create a composite index matching the WHERE and ORDER BY columns.

CREATE INDEX idx_orders_cust_created
ON orders (customer_id, created_at DESC);

Reading the Plan

A well-optimized LATERAL shows a Nested Loop with an Index Scan on the inner side. If you see a Seq Scan inside the loop, the supporting index is missing.

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

LATERAL with Set-Returning Functions

LATERAL also works with functions like unnest or jsonb_array_elements, expanding array columns per row.

SELECT p.id, tag
FROM products p
CROSS JOIN LATERAL unnest(p.tags) AS tag;

Top-N Per Group Pattern

The most common use of LATERAL is the top-N-per-group query. It is usually faster than window-function approaches when N is small and an index supports the order.

When to Avoid LATERAL

If the outer table is huge and the inner subquery has no supporting index, running it millions of times is slow. In that case a window function or a single aggregated join may win. Always measure both.

Passing Computed Values Forward

LATERAL can also compute an intermediate value and reuse it in later expressions, avoiding repeating the same calculation. Each LATERAL block sees the columns produced before it.

SELECT o.id, m.margin
FROM orders o
CROSS JOIN LATERAL (
  SELECT o.total - o.cost AS margin
) m
WHERE m.margin > 0;

Quick Check

Test your LATERAL knowledge.

Recap

You learned LATERAL joins:

  • They let a FROM-clause subquery see earlier tables' columns
  • Perfect for top-N-per-group and per-row lookups
  • Use LEFT JOIN LATERAL ... ON true to keep unmatched outer rows
  • Back the inner filter and sort with a composite index
  • Confirm a Nested Loop + Index Scan in the plan

Frequently asked questions

Is the “Optimizing LATERAL Joins and Correlated Lookups” lesson free?

Yes — the full text of “Optimizing LATERAL Joins and Correlated Lookups” is free to read here on the web, and the PostgreSQL Performance & Query Optimization course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the PostgreSQL Performance & Query Optimization course, upgrade to CoddyKit PRO.

What will I learn in “Optimizing LATERAL Joins and Correlated Lookups”?

Learn how LATERAL joins let a subquery reference columns from earlier tables, and how to use them to replace slow correlated subqueries with efficient per-row lookups. You practise PostgreSQL Performance & Query Optimization with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start PostgreSQL Performance & Query Optimization?

No prior experience is required. PostgreSQL Performance & Query Optimization on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Optimizing LATERAL Joins and Correlated Lookups” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this PostgreSQL Performance & Query Optimization lesson?

Yes. Every PostgreSQL Performance & Query Optimization lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Understanding Join Algorithms
  2. Rewriting Complex Joins
  3. Subquery vs. CTE vs. Joins
  4. Optimizing LATERAL Joins and Correlated Lookups
← Back to PostgreSQL Performance & Query Optimization