The Power of SQL LATERAL Joins (and CROSS APPLY)

If you come from a programming background, you're used to "For Loops"—iterating over a list and doing something for each item. SQL operates on sets, so we usually avoid thinking in loops.

However, sometimes you explicitly need to say: "For every row in Table A, run this specific subquery on Table B."

This is exactly what the LATERAL keyword (in PostgreSQL/MySQL) or CROSS APPLY (in SQL Server/Oracle) enables. It allows a subquery in the FROM clause to reference columns from preceding tables.

The Problem: Top N Per Group

Imagine you want to find the Top 3 most recent orders for EACH customer.

In standard SQL, you might use a Window Function:

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date DESC) as rn
    FROM orders
) sub
WHERE rn <= 3;

This works, but it calculates the rank for every single order in the database before filtering. If you have billions of orders but only want the top 3 for 10 specific users, this is incredibly inefficient.

The Solution: LATERAL Join

With a LATERAL join, the database can essentially loop over your specific users and fetch only the 3 matching rows for index scan.

-- PostgreSQL Syntax
SELECT u.name, o.order_date, o.amount
FROM users u
JOIN LATERAL (
    SELECT *
    FROM orders
    WHERE user_id = u.id -- correlating to the outer table!
    ORDER BY order_date DESC
    LIMIT 3
) o ON true;

Notice the magic: the subquery uses u.id. A normal subquery in a JOIN cannot see logic from the table before it. LATERAL breaks this wall.

Use Case 2: Exploding Data

LATERAL is also amazing for on-the-fly calculations. Imagine you have a complex calculation that you want to reuse in the SELECT list and the WHERE clause without repeating the code.

SELECT
    product_id,
    metrics.net_profit
FROM sales
CROSS JOIN LATERAL (
    SELECT
        price - tax - shipping_cost as net_profit
) metrics
WHERE metrics.net_profit > 100;

Interactive Playground

Note: The playground runs on SQLite, which doesn't support the LATERAL keyword directly. Below, we demonstrate the efficient "Top N" result using the standard ROW_NUMBER() approach, which is the best cross-platform alternative.

Conclusion

If you are using PostgreSQL or SQL Server, LATERAL (or CROSS APPLY) is a game-changer for performance tuning and writing cleaner, more modular queries. It bridges the gap between set-based SQL and procedural programming, giving you the best of both worlds.