Optimizing LATERAL Joins and Correlated Lookups
Learn how LATERAL joins let a subquery reference columns from earlier tables, and how to use them to replace slow correlated subqueries with efficient per-row lookups.
What is a LATERAL Join?
A LATERAL join lets a subquery in the FROM clause reference columns from tables listed before it. Without LATERAL, a subquery in FROM cannot see the outer rows.
The Problem It Solves
Suppose you want, for each customer, their three most recent orders. A plain join cannot easily limit rows per customer. LATERAL evaluates the subquery once per outer row, so a per-row LIMIT works.
All lessons in this course
- Understanding Join Algorithms
- Rewriting Complex Joins
- Subquery vs. CTE vs. Joins
- Optimizing LATERAL Joins and Correlated Lookups