阅读中文版本

LATERAL Joins

A LATERAL join lets a subquery reference columns from rows earlier in the FROM clause, turning a correlated subquery into a set-producing join.

Definition

LATERAL is a SQL construct that allows a subquery in the FROM clause to reference columns of preceding items, so it is evaluated per outer row and can return multiple rows.

It is used for top-N-per-group queries, table functions, and rewriting correlated scalar subqueries as joins. Compared to simpler alternatives, this concept fits best when the trade-off it represents is the one the system actually cares about.

When to use it

A common use case is applying it at the boundary where the cost of getting it wrong is highest, and the scenario usually involves a concrete guarantee the system must uphold. Unlike an ad hoc approach, it makes that guarantee explicit, reviewable, and testable.

How it works

Because the subquery can see columns of the item to its left, a lateral join expresses top-N-per-group naturally, for example selecting the three most recent orders for each customer.

On engines without a lateral keyword the same effect comes from a correlated subquery, but the planner may evaluate it differently and can fail to stream results efficiently.

Trade-offs and alternatives

A lateral join with no restricting predicate degenerates into a cross join, so always pair it with an ORDER BY plus LIMIT or a WHERE clause inside the subquery. The main drawback is the extra complexity it introduces, so the benefit should clearly outweigh that cost before a team adopts it.

Common misconceptions

A persistent misconception is that a lateral join is just a comma-separated cross join. Another is that lateral joins are always faster than correlated subqueries. For example, an engineer who assumes otherwise may design around a guarantee that does not actually exist, which only becomes obvious under load or failure.

Anti-patterns to avoid

Two recurring anti-patterns are to using lateral where a plain join would express the intent and to forgetting that a lateral join without a condition can multiply rows. Both tend to surface as hard-to-debug production issues rather than obvious test failures. Related concepts include Upsert with INSERT … ON CONFLICT, The RETURNING Clause, GIN Indexes for Composite Values.