Mastering Lateral Joins
In this lesson, you will learn how to use LATERAL joins to execute subqueries that reference columns from preceding table expressions, allowing you to perform dynamic calculations for each row in your main result set.
The Problem with Standard Joins
Usually, when you write a JOIN, the right-hand side of the join is an independent table or subquery. It doesn't "know" anything about the specific row it is currently processing from the left-hand table. If you wanted to run a complex calculation (like finding the most recent order for a specific user) for every row in a list of users, a standard join would struggle because you cannot easily pass the user_id into the join logic.
Enter the Lateral Join
A LATERAL join acts like a foreach loop. It allows a subquery to see the columns provided by the tables that appear to its left. When the database engine processes the query, it takes one row from the left table, feeds the values into the LATERAL subquery, executes it, and returns the result for that specific row.
Consider a scenario where you have a list of customers and you want to retrieve their two most recent orders:
SELECT c.name, o.order_date, o.total
FROM customers c
CROSS JOIN LATERAL (
SELECT order_date, total
FROM orders
WHERE orders.customer_id = c.id
ORDER BY order_date DESC
LIMIT 2
) o;
In this example, the subquery isn't just a static table. Because of the LATERAL keyword, the orders subquery is re-evaluated for every customer in the customers table, using that specific customer's id.
This is incredibly powerful for advanced data aggregation. You can use it to perform "top-N" style queries, apply complex window-like logic that isn't easily handled by standard partitions, or perform custom transformations on a per-row basis that rely on external data lookups. It turns a static join into a dynamic, row-aware function.
Key Takeaway: A LATERAL join allows you to execute a subquery for each row, enabling you to pass column values from preceding tables into the subquery logic for row-specific processing.