The SQL Superpower You’re Not Using: A Guide to LATERAL Joins

If you’ve ever felt limited by standard INNER or LEFT joins, you aren’t alone. Traditional joins act like a static Venn diagram—they combine two tables based on a predefined condition, but they can't "talk" to each other row-by-row during the process.

Enter the LATERAL JOIN. Introduced in PostgreSQL 9.3 and now a staple of modern SQL, it is often described as a SQL foreach loop.

What is a LATERAL Join?

A LATERAL join allows a subquery in the FROM clause to reference columns from preceding tables in the same FROM list.

In a standard join, subqueries are evaluated in isolation. They don't know the "current row" of the main table exists. With the LATERAL keyword, you break that wall, allowing the subquery to calculate results dynamically for every single row of the left-hand table.

Top 3 Use Cases

The "Top N" Per Category Problem

This is the classic use case. Imagine you have a Categories table and a Products table. You want to find the 3 most expensive products for every category.

  • Without Lateral: You’d need complex Window Functions (ROW_NUMBER()) and a wrapper query.
  • With Lateral: You simply write a subquery that selects the top 3 products and "connects" it to the category ID laterally.

Calculations on Calculated Columns

If you calculate a value in your query (like a complex tax formula) and want to use that result in another calculation in the same row, standard SQL makes you repeat the code or use a CTE.

A LATERAL join lets you define the calculation once and reference it like a variable.

Expanding JSONB or Arrays

If you store data in JSONB blobs or arrays, LATERAL is your best friend for "un-nesting" that data while keeping it attached to its parent record.

LATERAL in Action: The Syntax

Here is how it looks in practice when fetching the most recent order for every customer:

SELECT 
    c.customer_name, 
    recent_order.order_date, 
    recent_order.total_amount
FROM customers c
CROSS JOIN LATERAL (
    SELECT order_date, total_amount
    FROM orders o
    WHERE o.customer_id = c.id  -- This reference is only possible with LATERAL
    ORDER BY order_date DESC
    LIMIT 1
) AS recent_order;

Rewriting a Complex Query: The Subscription Problem

Fetching each user's name, their most recent payment, and the difference between that and their previous payment.

The "Old" Way: Window Functions & CTEs

Before LATERAL, you’d likely use Common Table Expressions (CTEs) and LAG() to peek at previous rows. It works, but it’s dense and requires multiple scans of the data.

WITH RankedPayments AS (
    SELECT 
        user_id,
        amount,
        LAG(amount) OVER (PARTITION BY user_id ORDER BY created_at DESC) as prev_amount,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) as rn
    FROM billing_events
)
SELECT 
    u.name,
    rp.amount AS current_payment,
    rp.prev_amount AS last_payment,
    (rp.amount - rp.prev_amount) AS delta
FROM users u
JOIN RankedPayments rp ON u.id = rp.user_id
WHERE rp.rn = 1;

The "LATERAL" Way: Clean & Modular

With a LATERAL join, you treat the "latest two payments" as a little package you fetch for every user. It reads like a story: "For every user, find their last two bills, then calculate the difference."

SELECT 
    u.name,
    bills.current_payment,
    bills.last_payment,
    (bills.current_payment - bills.last_payment) AS delta
FROM users u
LEFT JOIN LATERAL (
    SELECT 
        amount AS current_payment,
        -- Sub-subquery to get the second most recent bill
        (SELECT amount FROM billing_events b2 
         WHERE b2.user_id = u.id 
         ORDER BY created_at DESC LIMIT 1 OFFSET 1) AS last_payment
    FROM billing_events b1
    WHERE b1.user_id = u.id
    ORDER BY created_at DESC
    LIMIT 1
) bills ON TRUE;

Why the LATERAL version wins here:

  1. Index Optimization: If user_id and created_at are indexed, Postgres can perform a very fast "Index Scan" for each user rather than sorting the entire billing_events table to partition it.
  2. Readability: You don't have to keep track of WHERE rn = 1 at the very end of your query; the logic is encapsulated within the join.
  3. Flexibility: If you suddenly need to add a third column (like the average of all previous payments for just that user), you can drop that logic right into the LATERAL block without breaking the rest of the query.

The Bottom Line

The LATERAL join is a tool that moves SQL closer to procedural programming logic without sacrificing the declarative power of the database. It simplifies complex queries and, when paired with the right indexes, can significantly boost performance for per-row calculations.

Read more