LATERAL subqueries in PostgreSQL - Deep Dive

Overview - LATERAL subqueries

What is it?

A LATERAL subquery in PostgreSQL is a special kind of subquery that can refer to columns from tables listed before it in the FROM clause. It allows each row from the preceding table to be used as input for the subquery, producing results that depend on that row. This makes it possible to perform row-by-row computations or fetch related data dynamically within a single query.

Why it matters

Without LATERAL subqueries, it would be hard or inefficient to write queries that need to use data from one row to compute or fetch related rows dynamically. This feature solves the problem of combining row-specific calculations with set-based operations, enabling more powerful and readable queries. Without it, developers might resort to multiple queries or complex joins that are harder to write and maintain.

Where it fits

Before learning LATERAL subqueries, you should understand basic SQL SELECT statements, JOINs, and subqueries. After mastering LATERAL, you can explore advanced SQL features like window functions, recursive queries, and set-returning functions to write even more powerful queries.

Mental Model

Core Idea

A LATERAL subquery lets each row from a previous table feed into a subquery, producing results that depend on that specific row.

Think of it like...

Imagine a chef tasting each ingredient before deciding how to prepare a small dish for that ingredient. The chef uses the ingredient's flavor to customize the dish, just like a LATERAL subquery uses each row's data to customize its output.

FROM clause flow:

┌─────────────┐
│  Table A    │
└─────┬───────┘
      │ rows flow into
┌─────▼────────┐
│ LATERAL Subq │
│ (uses Table A│
│  row values) │
└──────────────┘
      │ outputs rows
      ▼
 Result set combines both

Build-Up - 7 Steps

1

Foundation

Basic SQL FROM and Subqueries

Concept: Understanding how the FROM clause and subqueries work in SQL.

In SQL, the FROM clause lists tables or subqueries to get data from. A subquery is a query inside another query, often used to filter or compute values. Normally, subqueries cannot see columns from tables listed alongside them in the FROM clause.

Result

You can select data from tables and use subqueries, but subqueries cannot refer to sibling tables in the FROM clause.

Knowing the limits of normal subqueries sets the stage to appreciate why LATERAL is needed.

2

Foundation

Understanding Table Joins

Concept: How tables combine rows using JOINs in SQL.

JOINs combine rows from two or more tables based on related columns. For example, INNER JOIN returns rows where keys match. JOINs work row-by-row but do not allow subqueries to dynamically use each row's data inside the FROM clause.

Result

You can combine tables to get related data, but JOINs alone cannot perform row-dependent subqueries.

Recognizing JOIN limitations helps understand why LATERAL subqueries are a powerful extension.

3

Intermediate

Introducing LATERAL Keyword

🤔Before reading on: do you think a subquery in FROM can use columns from tables listed after it? Commit to yes or no.

Concept: LATERAL allows a subquery to refer to columns from tables listed before it in the FROM clause.

Normally, subqueries in FROM cannot see columns from other tables. Adding LATERAL before a subquery tells PostgreSQL to run the subquery for each row of the preceding table, letting it use that row's columns. This enables dynamic, row-specific computations inside a single query.

Result

The subquery runs once per row from the previous table, producing results customized for each row.

Understanding that LATERAL changes subquery scope unlocks powerful row-wise operations within set-based SQL.

4

Intermediate

Using LATERAL with Functions

🤔Before reading on: do you think set-returning functions can be used with LATERAL to produce multiple rows per input row? Commit to yes or no.

Concept: LATERAL can be combined with set-returning functions to generate multiple rows per input row dynamically.

PostgreSQL functions that return sets of rows can be called in the FROM clause with LATERAL. For each row from the previous table, the function runs and returns multiple rows, which are joined to the original row. This is useful for expanding arrays or JSON data into rows.

Result

The query outputs multiple rows per original row, expanding nested data dynamically.

Knowing how LATERAL works with functions reveals how to flatten complex data structures elegantly.

5

Intermediate

LATERAL vs Correlated Subqueries

🤔Before reading on: do you think LATERAL subqueries and correlated subqueries are the same? Commit to yes or no.

Concept: LATERAL subqueries are similar to correlated subqueries but appear in the FROM clause and can return multiple columns and rows.

Correlated subqueries appear in WHERE or SELECT clauses and depend on outer query rows. LATERAL subqueries appear in FROM and can return multiple columns and rows per input row. This makes LATERAL more flexible for complex row-wise operations.

Result

You can write more readable and powerful queries using LATERAL instead of complex correlated subqueries.

Distinguishing LATERAL from correlated subqueries clarifies when to use each for better query design.

6

Advanced

Performance Considerations with LATERAL

🤔Before reading on: do you think LATERAL subqueries always perform worse than JOINs? Commit to yes or no.

Concept: LATERAL subqueries can be efficient but may cause performance issues if not used carefully, especially with large datasets.

Because LATERAL runs the subquery once per row of the preceding table, it can be costly if that table is large. Proper indexing and limiting rows before LATERAL help. Sometimes rewriting queries or using JOINs is better for performance.

Result

Well-written LATERAL queries perform well; careless use can slow queries significantly.

Understanding LATERAL's execution model helps write queries that balance power and performance.

7

Expert

Advanced Uses and Internals of LATERAL

🤔Before reading on: do you think LATERAL subqueries are executed once or multiple times internally? Commit to your answer.

Concept: LATERAL subqueries are executed once per input row, allowing dynamic row-wise computation, and PostgreSQL's planner optimizes their execution carefully.

Internally, PostgreSQL treats LATERAL subqueries as functions called for each row of the preceding table. The planner can push down filters and optimize joins to reduce overhead. Advanced users can combine LATERAL with window functions and CTEs for complex analytics.

Result

Queries using LATERAL can be both expressive and efficient when designed with internal behavior in mind.

Knowing LATERAL's internal execution model enables writing advanced queries that leverage PostgreSQL's optimizer fully.

Under the Hood

LATERAL subqueries are implemented as per-row function calls during query execution. For each row from the preceding table in the FROM clause, PostgreSQL evaluates the LATERAL subquery using that row's column values as input. This allows the subquery to produce a set of rows customized per input row. The query planner integrates these calls into the overall execution plan, optimizing join order and filter pushdown to minimize overhead.

Why designed this way?

LATERAL was introduced to overcome the limitations of standard subqueries and JOINs that cannot dynamically use data from each row in the FROM clause. It provides a clean, declarative way to express row-dependent computations without resorting to procedural code or multiple queries. The design balances expressiveness with the ability for the planner to optimize execution.

Query execution flow:

┌─────────────┐
│ Outer Table │
│  (input)    │
└─────┬───────┘
      │ For each row
      ▼
┌─────────────┐
│ LATERAL Subq│
│ (per-row)   │
└─────┬───────┘
      │ Returns rows
      ▼
┌─────────────┐
│ Join Output │
└─────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does a LATERAL subquery run only once per query or once per row? Commit to your answer.

Common Belief: LATERAL subqueries run only once per query, like normal subqueries.

Reality: LATERAL subqueries run once per row of the preceding table, allowing row-specific results.

Why it matters: Assuming LATERAL runs once leads to wrong expectations about performance and results, causing inefficient queries or bugs.

Quick: Can a LATERAL subquery refer to tables listed after it in the FROM clause? Commit to yes or no.

Common Belief: LATERAL subqueries can refer to any table in the FROM clause, regardless of order.

Reality: LATERAL subqueries can only refer to tables listed before them in the FROM clause.

Why it matters: Misunderstanding this causes syntax errors and confusion about query structure.

Quick: Are LATERAL subqueries and correlated subqueries interchangeable? Commit to yes or no.

Common Belief: LATERAL subqueries and correlated subqueries are the same and can be used interchangeably.

Reality: They differ in syntax, placement, and capabilities; LATERAL appears in FROM and can return multiple rows and columns, while correlated subqueries appear in WHERE or SELECT and usually return single values.

Why it matters: Confusing them leads to poor query design and missed opportunities for clearer, more efficient queries.

Quick: Does using LATERAL always degrade query performance? Commit to yes or no.

Common Belief: LATERAL subqueries always make queries slower because they run per row.

Reality: While LATERAL can be costly if misused, with proper indexing and query design, it can be efficient and sometimes faster than alternatives.

Why it matters: Avoiding LATERAL due to fear of performance can limit query expressiveness and lead to more complex, less maintainable code.

Expert Zone

1

LATERAL subqueries can be combined with window functions inside the subquery for powerful per-row analytics.

2

The PostgreSQL planner can sometimes inline LATERAL subqueries or reorder joins to optimize performance, but this depends on query complexity.

3

Using LATERAL with set-returning functions allows elegant expansion of arrays or JSON fields into rows without complex joins.

When NOT to use

Avoid LATERAL when the preceding table is very large and the subquery is expensive per row; consider rewriting with JOINs or using materialized views. Also, if the subquery does not depend on the preceding table's row, a normal JOIN or subquery is simpler and more efficient.

Production Patterns

In production, LATERAL is used to expand JSON arrays into rows, fetch top-N related records per row, and perform dynamic calculations per row. It is common in analytics queries, ETL pipelines, and APIs that need flexible data shaping.

Connections

Correlated Subqueries

LATERAL subqueries build on the idea of correlated subqueries but appear in the FROM clause and can return multiple rows.

Understanding correlated subqueries helps grasp how LATERAL extends row-dependent querying to more flexible and powerful patterns.

Functional Programming Closures

LATERAL subqueries behave like closures that capture variables (columns) from an outer scope (preceding table row).

Recognizing this similarity helps understand how LATERAL dynamically uses outer row data inside the subquery.

Map-Reduce Paradigm

LATERAL subqueries map each input row to a set of output rows, similar to the map step in Map-Reduce.

Seeing LATERAL as a map operation clarifies its role in transforming and expanding data row-wise within SQL.

Common Pitfalls

#1 Using LATERAL subquery without referencing preceding table columns.

Wrong approach:

SELECT a.id, b.val FROM table_a a, LATERAL (SELECT val FROM table_b WHERE id = 1) b;

Correct approach:

SELECT a.id, b.val FROM table_a a, LATERAL (SELECT val FROM table_b WHERE id = a.id) b;

Root cause:

Not linking the LATERAL subquery to the preceding table row defeats its purpose and may cause unexpected results.

#2 Placing LATERAL subquery before the table it depends on.

Wrong approach:

SELECT * FROM LATERAL (SELECT val FROM table_b WHERE id = a.id) b, table_a a;

Correct approach:

SELECT * FROM table_a a, LATERAL (SELECT val FROM table_b WHERE id = a.id) b;

Root cause:

LATERAL subqueries can only refer to tables listed before them; reversing order causes syntax errors.

#3 Using LATERAL subquery on very large tables without filters.

Wrong approach:

SELECT a.id, b.val FROM large_table a, LATERAL (SELECT val FROM other_table WHERE ref = a.id) b;

Correct approach:

SELECT a.id, b.val FROM (SELECT * FROM large_table WHERE condition) a, LATERAL (SELECT val FROM other_table WHERE ref = a.id) b;

Root cause:

Not limiting rows before LATERAL causes the subquery to run many times, leading to poor performance.

Key Takeaways

LATERAL subqueries let you write queries where each row from one table feeds into a subquery that can produce multiple rows or columns based on that row.

They extend SQL's power by enabling dynamic, row-wise computations inside the FROM clause, which normal subqueries and JOINs cannot do alone.

Understanding LATERAL's execution model helps write efficient queries and avoid common performance pitfalls.

LATERAL is distinct from correlated subqueries and is especially useful with set-returning functions and complex data structures like JSON or arrays.

Mastering LATERAL opens doors to advanced SQL patterns used in real-world analytics, ETL, and API data shaping.