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.

Practice

1. What is the main purpose of using LATERAL in a PostgreSQL query?

easy

A. To allow a subquery to reference columns from tables listed before it in the FROM clause

B. To create a temporary table that lasts only for the duration of the query

C. To optimize the query by parallelizing subqueries automatically

D. To enforce foreign key constraints between tables

Solution

1. Step 1: Understand the role of LATERAL

   LATERAL allows a subquery to access columns from tables that appear earlier in the FROM clause.

2. Step 2: Compare with other options

   The other options describe different features unrelated to LATERAL's purpose.

3. Final Answer:

   To allow a subquery to reference columns from tables listed before it in the FROM clause -> Option A

4. Quick Check:

   LATERAL = subquery uses previous table columns [OK]

Hint: LATERAL lets subqueries see earlier tables in FROM [OK]

Common Mistakes:

• Thinking LATERAL creates temporary tables
• Confusing LATERAL with query parallelism
• Assuming LATERAL enforces constraints

2. Which of the following is the correct syntax to use a LATERAL subquery in PostgreSQL?

easy

A. SELECT * FROM table1, (SELECT * FROM table2 WHERE table2.id = table1.id) sub;

B. SELECT * FROM table1 JOIN LATERAL table2 ON table2.id = table1.id;

C. SELECT * FROM table1 CROSS JOIN LATERAL (SELECT * FROM table2 WHERE table2.id = table1.id) sub;

D. SELECT * FROM table1 LATERAL JOIN table2 ON table2.id = table1.id;

Solution

1. Step 1: Identify correct LATERAL syntax

   The correct syntax uses CROSS JOIN LATERAL with a subquery in parentheses and an alias.

2. Step 2: Check each option

   SELECT * FROM table1, (SELECT * FROM table2 WHERE table2.id = table1.id) sub; misses LATERAL keyword causing reference error; SELECT * FROM table1 JOIN LATERAL table2 ON table2.id = table1.id; uses JOIN LATERAL incorrectly without parentheses; SELECT * FROM table1 LATERAL JOIN table2 ON table2.id = table1.id; has invalid syntax.

3. Final Answer:

   SELECT * FROM table1 CROSS JOIN LATERAL (SELECT * FROM table2 WHERE table2.id = table1.id) sub; -> Option C

4. Quick Check:

   Correct LATERAL syntax = SELECT * FROM table1 CROSS JOIN LATERAL (SELECT * FROM table2 WHERE table2.id = table1.id) sub; [OK]

Hint: Use CROSS JOIN LATERAL with subquery in parentheses [OK]

Common Mistakes:

• Omitting LATERAL keyword
• Not aliasing the subquery
• Using LATERAL without parentheses

3. Given tables users(id, name) and orders(user_id, amount), what does this query return?

SELECT u.name, o.total FROM users u CROSS JOIN LATERAL (SELECT SUM(amount) AS total FROM orders WHERE user_id = u.id) o;

medium

A. Each user with the total amount of their orders

B. All users joined with all orders without filtering

C. Only users who have orders, with their order totals

D. Syntax error due to missing GROUP BY

Solution

1. Step 1: Analyze the LATERAL subquery

   The subquery sums orders per user using user_id = u.id, so it calculates total per user.

2. Step 2: Understand CROSS JOIN LATERAL effect

   It runs the subquery for each user, returning their name and total order amount, including users with zero orders (total will be NULL).

3. Final Answer:

   Each user with the total amount of their orders -> Option A

4. Quick Check:

   LATERAL sums orders per user = Each user with the total amount of their orders [OK]

Hint: LATERAL runs subquery per row, here summing orders per user [OK]

Common Mistakes:

• Thinking it returns all orders without grouping
• Assuming only users with orders appear
• Confusing CROSS JOIN LATERAL with INNER JOIN

4. Identify the error in this query using LATERAL:

SELECT t1.id, t2.val FROM table1 t1 JOIN LATERAL (SELECT val FROM table2 WHERE table2.ref = t1.id) t2;

medium

A. Missing alias for the LATERAL subquery

B. JOIN LATERAL requires CROSS keyword

C. Subquery must return only one column

D. No error; query is correct

Solution

1. Step 1: Check alias usage

   The subquery is aliased as t2, so no issue there.

2. Step 2: Verify JOIN LATERAL syntax

   JOIN LATERAL without CROSS is valid syntax in PostgreSQL; CROSS JOIN LATERAL is one form, but JOIN LATERAL alone is allowed as INNER JOIN LATERAL.

3. Step 3: Confirm subquery column count

   Subquery returns one column 'val', which is acceptable.

4. Final Answer:

   No error; query is correct -> Option D

5. Quick Check:

   JOIN LATERAL syntax without CROSS is valid = No error [OK]

Hint: JOIN LATERAL without CROSS is valid syntax [OK]

Common Mistakes:

• Using JOIN LATERAL without CROSS
• Forgetting subquery alias
• Expecting subquery to return multiple columns

5. You want to list each customer with their top 2 most expensive orders using LATERAL. Which query correctly achieves this?

SELECT c.name, o.order_id, o.amount FROM customers c CROSS JOIN LATERAL (SELECT order_id, amount FROM orders WHERE customer_id = c.id ORDER BY amount DESC LIMIT 2) o;

hard

A. The query misses GROUP BY on customers

B. This query correctly lists each customer with their top 2 orders

C. LATERAL cannot be used with LIMIT inside subqueries

D. The query will return only customers with at least 2 orders

Solution

1. Step 1: Understand LATERAL with LIMIT

   The subquery selects top 2 orders per customer by ordering and limiting results.

2. Step 2: Check if query returns all customers

   CROSS JOIN LATERAL runs subquery per customer; if no orders, no rows for that customer appear.

3. Step 3: Confirm correctness

   The query correctly lists each customer with up to 2 highest orders; no syntax or logic errors.

4. Final Answer:

   This query correctly lists each customer with their top 2 orders -> Option B

5. Quick Check:

   LATERAL with LIMIT works for top N per row [OK]

Hint: Use LATERAL with ORDER BY and LIMIT for top N per row [OK]

Common Mistakes:

• Thinking GROUP BY is needed here
• Believing LATERAL disallows LIMIT
• Assuming all customers appear even without orders