A join that sees its left side
Normally a subquery in the FROM clause cannot reference other tables in the same FROM. A LATERAL join lifts that restriction: the lateral subquery can use columns from the tables listed before it, evaluated once per outer row. Some engines call the equivalent feature CROSS APPLY and OUTER APPLY.
The top N per group use case
The classic motivation is top N per group. For each customer you want their three most recent orders. A lateral subquery references the current customer id, applies ORDER BY date with LIMIT three, and returns those rows joined back to the customer.
- The left table supplies each customer.
- The LATERAL subquery runs per customer, ordered and limited.
- The result is a clean top N per group without window function gymnastics.
CROSS versus LEFT LATERAL
- CROSS JOIN LATERAL drops outer rows whose subquery returns nothing.
- LEFT JOIN LATERAL keeps every outer row, padding missing subquery columns with nulls, like a normal outer join.
When to reach for it
LATERAL shines for per row table valued computations: expanding arrays per row, calling set returning functions, or correlated LIMIT queries that plain joins cannot express.
Key idea
A LATERAL join lets a FROM clause subquery reference earlier tables and run per outer row, making top N per group and per row table valued calls natural to express.