LATERAL subqueries in PostgreSQL - Step-by-Step Execution

Concept Flow - LATERAL subqueries

Start with main table row

↓

For each row, run lateral subquery

↓

Subquery can use main row's columns

↓

Combine main row + subquery result

↓

Repeat for all rows

↓

Return combined result set

LATERAL runs a subquery for each row of the main query, letting the subquery use columns from that row.

Execution Sample

PostgreSQL

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

For each customer, find one order using a lateral subquery that can access the customer's id.

Execution Table

Step	Main Row (c.name, c.id)	Lateral Subquery Query	Subquery Result (order_id)	Combined Output
1	('Alice', 1)	SELECT order_id FROM orders o WHERE customer_id=1 LIMIT 1	101	('Alice', 101)
2	('Bob', 2)	SELECT order_id FROM orders o WHERE customer_id=2 LIMIT 1	201	('Bob', 201)
3	('Charlie', 3)	SELECT order_id FROM orders o WHERE customer_id=3 LIMIT 1	NULL	('Charlie', NULL)
4	No more rows	No subquery run	No result	End of result set

💡 All customer rows processed; subquery ran for each; query ends.

Variable Tracker

Variable	Start	After 1	After 2	After 3	Final
c.name	N/A	Alice	Bob	Charlie	End
c.id	N/A	1	2	3	End
o.order_id	N/A	101	201	NULL	End

Key Moments - 2 Insights

Why does the lateral subquery run once per main row instead of once for the whole table?

Because LATERAL allows the subquery to use columns from the current main row, it must run separately for each row to get correct results, as shown in execution_table rows 1-3.

What happens if the lateral subquery returns no rows for a main row?

The subquery returns NULL for that row's columns, so the combined output shows NULL, like for 'Charlie' in execution_table row 3.

Visual Quiz - 3 Questions

Look at the execution_table, what is the subquery result for the second main row?

A 101

B 201

C NULL

D No result

💡 Hint

Check execution_table row 2 under 'Subquery Result (order_id)'

At which step does the lateral subquery return NULL?

A Step 3

B Step 2

C Step 1

D Step 4

💡 Hint

Look at execution_table row 3 for 'Subquery Result (order_id)'

If the lateral keyword is removed, what changes in the execution?

A Query will fail with syntax error

B Subquery runs once per row but cannot access main row columns

C Subquery can no longer use main row columns, so it runs once for all rows

D No change, same results

💡 Hint

LATERAL allows subquery to access main row columns; without it, subquery is independent

Concept Snapshot

LATERAL subqueries run once per main query row.
They can use columns from that row.
Syntax: FROM main_table LEFT JOIN LATERAL (subquery) alias ON true
Useful for dependent subqueries.
Returns combined rows with subquery results.
If subquery returns no rows, columns are NULL.

Full Transcript

LATERAL subqueries in PostgreSQL let you run a subquery for each row of a main table. This subquery can use columns from the current main row. For example, if you have a customers table and want to find one order per customer, you can write a lateral subquery that selects orders where the customer_id matches the current customer's id. The execution flow starts by taking a row from the main table, then running the lateral subquery using that row's data, then combining the results. This repeats for all rows. If the subquery finds no matching rows, it returns NULL for that row's subquery columns. This behavior is shown in the execution table where for 'Charlie' no order is found, so NULL appears. Without LATERAL, the subquery cannot access main row columns and runs independently. This makes LATERAL very useful for queries needing row-dependent subqueries.

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