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.