The Lateral Join
A lateral join lets the right side of a join reference columns from the current row of the left side. Normally a join subquery cannot see the outer row, but LATERAL removes that barrier.
Why it is useful
Lateral joins express per row computations that are awkward otherwise, such as fetching the top three orders for each customer. For every left row, the engine evaluates the right subquery using that row as input, like a correlated subquery that can return many columns and rows.
How it executes
The executor walks the left input and, for each row, runs the right side parameterized by that row, then joins the results. This resembles a nested loop where the inner side is a fresh subquery per outer row.
- The right side may reference left side columns.
- It is ideal for top N per group style queries.
- It executes like a nested loop with a correlated inner query.
Key idea
A lateral join evaluates its right side once per left row using that row as input, making per row subqueries like top N per group natural to express.