Alright, let’s talk about LATERAL. This is the one that makes people feel like they’ve leveled up. It’s the JOIN that lets you break the most fundamental rule of SQL you’ve probably internalized: that everything in your FROM clause happens in its own little isolated bubble.

Think of a standard subquery in your SELECT list. It can’t see the individual row from the main table it’s being compared to until after the main FROM clause has done its thing. It’s like shouting questions to a friend in another room. A LATERAL join, on the other hand, is like turning to the person right next to you and asking a question for each row. It’s a correlated subquery on steroids, and it’s executed row-by-row alongside the table it’s joining to.

The magic, and the reason it has a special keyword, is that it allows the subquery to explicitly reference columns from preceding tables in the FROM clause. This is the “correlation” part. Without LATERAL, that’s simply not allowed. The database would throw its hands up in confusion. LATERAL is you giving the database permission to do this row-by-row evaluation.

The Basic Syntax: It’s Just a JOIN (But Fancy)

You’ll typically see it used right after a comma or a JOIN keyword. The subquery follows, wrapped in parentheses.

SELECT *
FROM customers c,
LATERAL (
    SELECT *
    FROM orders o
    WHERE o.customer_id = c.customer_id -- This is the magic!
    ORDER BY o.order_date DESC
    LIMIT 3
) AS latest_orders;

This is the classic use case: “For each customer, get me their three most recent orders.” Notice how the inner query (o.customer_id = c.customer_id) depends on the current row from the outer customers table. For each customer, the database stops, runs the LATERAL subquery using that specific customer_id, gets the result, and then moves on to the next customer.

You can, and often should, write this with the explicit CROSS JOIN LATERAL or LEFT JOIN LATERAL syntax. The LEFT variant is crucial—it means “even if the subquery returns no rows for this customer, still keep the customer row and just fill the columns from the lateral join with NULLs.” Our first example is an inner join; it would silently drop customers with no orders.

-- This keeps customers with zero orders!
SELECT c.customer_name, latest_orders.order_id
FROM customers c
LEFT JOIN LATERAL (
    SELECT o.order_id
    FROM orders o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.order_date DESC
    LIMIT 1
) AS latest_orders ON true;

The ON true might look bizarre, but it’s necessary. The ON clause for a LATERAL join is almost always just ON true because the correlation—the actual join condition—is already happening inside the subquery’s WHERE clause.

Why Not Just Use a Correlated Subquery?

You absolutely could try to write that “latest N orders” query with a regular correlated subquery in the SELECT list. But you’d immediately hit a wall: most SQL dialects won’t let you use LIMIT/TOP inside a scalar subquery. A LATERAL join, being a full-fledged table expression, lets you use ORDER BY, LIMIT, OFFSET, and even DISTINCT—things that are impossible in a simple scalar subquery. It’s the difference between asking for a single value and asking for an entire, complex result set per row.

The Power of Set-Generating Functions

This is where LATERAL truly shines and becomes irreplaceable. Imagine you have a table sales with a jsonb column called daily_metrics. It contains an array of daily numbers: [12, 44, 17].

How do you break that array out into individual rows? You use jsonb_array_elements or unnest. But you can’t just use them in the SELECT list; that would create an implicit cross join and you’d get a cartesian product if you had other rows. You need to do it safely, per row.

SELECT s.sale_id, s.sale_date, d.metric_value, d.metric_day
FROM sales s,
LATERAL jsonb_array_elements(s.daily_metrics) WITH ORDINALITY AS d(metric_value, metric_day);

For each row in sales, the LATERAL join calls the function jsonb_array_elements, passing in that specific row’s daily_metrics array. The function then generates a set of rows from it, which are seamlessly joined to the original sale. WITH ORDINALITY is a nice bonus that gives us the array index as a second column (metric_day). This pattern is flawless for working with JSON or array data.

The Performance Pitfall (And How to Avoid It)

Here’s the brutal truth: LATERAL can be a performance nightmare if used carelessly. Because it often forces a nested-loop join, it’s doing an entire subquery for each row in the outer table. If your outer table has a million rows, you’re running a million independent subqueries. If that subquery is even slightly heavy, everything grinds to a halt.

The best practice? Correlate on indexed columns. In our very first example, the inner query is WHERE o.customer_id = c.customer_id. If there’s an index on orders.customer_id, that million subqueries become a million very fast index lookups. Without that index, each of the million subqueries would be a full table scan, and your database will simply give up on life and move to a farm upstate. Always check the query plan. Use LATERAL deliberately, not as a catch-all solution. It’s a precision tool, not a hammer.