LATERAL join for correlated subqueries in PostgreSQL - Step-by-Step Execution

Concept Flow - LATERAL join for correlated subqueries

Start with main table

↓

For each row in main table

↓

Run subquery using current row values

↓

Join subquery result with main row

↓

Repeat for all rows

↓

Return combined result set

The LATERAL join runs a subquery for each row of the main table, using that row's values inside the subquery, then joins the subquery result back to the main 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 subquery that refers to the current customer row.

Execution Table

Step	Current customer (c.id, c.name)	Subquery executed	Subquery result (order_id)	Joined output row
1	(1, 'Alice')	SELECT order_id FROM orders WHERE customer_id=1 LIMIT 1	101	(Alice, 101)
2	(2, 'Bob')	SELECT order_id FROM orders WHERE customer_id=2 LIMIT 1	201	(Bob, 201)
3	(3, 'Carol')	SELECT order_id FROM orders WHERE customer_id=3 LIMIT 1	NULL (no orders)	(Carol, NULL)
4	No more customers	No subquery	No result	End of output

💡 All customers processed; subqueries run for each; output combined rows returned.

Variable Tracker

Variable	Start	After 1	After 2	After 3	Final
c.id	none	1	2	3	end
c.name	none	Alice	Bob	Carol	end
subquery order_id	none	101	201	NULL	end

Key Moments - 2 Insights

Why does the subquery use the current row's customer id?

Because the LATERAL join lets the subquery access columns from the current main table row, so it can filter orders for that specific customer (see execution_table steps 1-3).

What happens if the subquery returns no rows for a customer?

The join still outputs the main row with NULLs for the subquery columns, as shown for Carol in step 3 of the execution_table.

Visual Quiz - 3 Questions

Look at the execution_table, what is the subquery result for customer Bob at step 2?

A 201

B 101

C NULL

D No result

💡 Hint

Check the 'Subquery result (order_id)' column at step 2 in the execution_table.

At which step does the subquery return no orders?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look for 'NULL (no orders)' in the 'Subquery result' column in the execution_table.

If the subquery did not use LATERAL, what would happen?

A It would run faster

B It could not refer to the current customer row's id

C It would return all orders for all customers

D It would return no rows

💡 Hint

LATERAL allows the subquery to access columns from the main query's current row (see concept_flow).

Concept Snapshot

LATERAL join lets a subquery use columns from each row of the main table.
Syntax: FROM main_table LEFT JOIN LATERAL (subquery referencing main_table) ON condition.
Runs subquery once per main row, joining results.
Useful for correlated subqueries returning multiple columns.
If subquery returns no rows, main row still appears with NULLs.

Full Transcript

The LATERAL join in PostgreSQL runs a subquery for each row of the main table. This subquery can use values from the current row, making it a correlated subquery. For example, for each customer, the subquery finds one order belonging to that customer. The execution table shows each customer row, the subquery run with that customer's id, the order found (or NULL if none), and the combined output row. If the subquery returns no rows, the main row still appears with NULLs for the subquery columns. This is because LATERAL allows the subquery to access the current row's columns. Without LATERAL, the subquery could not refer to the main table's current row, so it would not filter orders per customer. This technique is useful to join each row with related data computed on the fly.