LATERAL Joins for Complex, Correlated Queries

1. Why a normal join is not enough here

An ordinary JOIN connects two tables via a condition, but the right side of the join is a fixed set computed independently of the current row on the left side. A subquery in the FROM clause is subject to the same restriction: it is evaluated once for the entire query and cannot access individual column values of the surrounding rows. Exactly that restriction is what a LATERAL join lifts.

A LATERAL join marks a subquery so that it may be re-evaluated for every row of the preceding table, with access to the column values of exactly that row. That is the decisive difference from a normal subquery in FROM: without LATERAL, the subquery would have to be completely independent of the outer query, with LATERAL it effectively becomes a function called once per row of the left table.

The name LATERAL join comes from the SQL standard, and PostgreSQL, MySQL 8.0.14+, and others implement it under exactly that keyword. SQL Server offers the same functionality under the names CROSS APPLY and OUTER APPLY, which semantically correspond to LATERAL combined with INNER JOIN or LEFT JOIN respectively. Oracle has also supported LATERAL since version 12c, and alternatively CROSS APPLY.

2. The syntax of LATERAL and CROSS APPLY

The syntax of a LATERAL join in PostgreSQL follows the pattern FROM table_a a, LATERAL (SELECT ... FROM table_b WHERE table_b.column = a.column) b, or explicitly with JOIN LATERAL ... ON true. The subquery's parentheses can access columns of a, which would cause an error in a normal, non-lateral subquery because a would not yet be in scope at that point in the query plan.

In SQL Server, the LATERAL keyword is dropped, and instead CROSS APPLY or OUTER APPLY is used directly in place of JOIN, without an additional ON condition, because the correlation already lives inside the referenced subquery. The behavior is functionally identical: for every row of the left table, the right subquery is re-evaluated with the current row's context.

-- PostgreSQL / MySQL 8: LATERAL join syntax
SELECT c.customer_id, c.name, recent.order_id, recent.amount
FROM customers c
JOIN LATERAL (
    SELECT order_id, amount
    FROM orders o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.order_date DESC
    LIMIT 1
) recent ON true;

-- SQL Server: CROSS APPLY syntax, functionally equivalent
SELECT c.customer_id, c.name, recent.order_id, recent.amount
FROM customers c
CROSS APPLY (
    SELECT TOP 1 order_id, amount
    FROM orders o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.order_date DESC
) recent;

3. Top-N per group: the classic use case

By far the most common use case for a LATERAL join is the top-N-per-group query: for every row of one table, the N related rows of a second table need to be found, sorted by some criterion, for example the three most recent orders per customer or the two cheapest offers per product. This task can only be solved with an ordinary JOIN through workarounds, whereas a LATERAL join expresses it directly and readably.

The subquery inside the LATERAL join contains exactly the logic you would write by hand for a single customer: filter by the correlated ID, sort by the desired criterion, cap it with LIMIT N or TOP N. This per-customer logic is then automatically repeated for every row of the outer table, without needing a window function followed by a filter on the rank.

-- Top 3 most recent orders per customer, using LATERAL
SELECT c.customer_id, c.name, o.order_id, o.order_date, o.amount
FROM customers c
JOIN LATERAL (
    SELECT order_id, order_date, amount
    FROM orders o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.order_date DESC
    LIMIT 3
) o ON true
ORDER BY c.customer_id, o.order_date DESC;

4. LEFT JOIN LATERAL and OUTER APPLY for empty results

A JOIN LATERAL behaves like an INNER JOIN by default: customers with no matching row in the correlated subquery, for example customers without orders, drop out of the result entirely. Anyone who wants to see all customers, including those without order history, needs LEFT JOIN LATERAL ... ON true in PostgreSQL or OUTER APPLY in SQL Server. In both cases the customer still appears in the output, with the subquery's columns filled with NULL.

This distinction is critical in practice for the correctness of reports: a query meant to show all customers including their latest activity, but that accidentally uses JOIN LATERAL instead of LEFT JOIN LATERAL, returns a result with missing customers, without throwing any error. The mistake is often only noticed when a row count in the report does not match the known total customer count.

-- LEFT JOIN LATERAL keeps customers with no orders, columns become NULL
SELECT c.customer_id, c.name, o.order_id, o.order_date
FROM customers c
LEFT JOIN LATERAL (
    SELECT order_id, order_date
    FROM orders o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.order_date DESC
    LIMIT 1
) o ON true;

-- SQL Server equivalent: OUTER APPLY
SELECT c.customer_id, c.name, o.order_id, o.order_date
FROM customers c
OUTER APPLY (
    SELECT TOP 1 order_id, order_date
    FROM orders o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.order_date DESC
) o;

5. Combining LATERAL with table functions

Another strong use case for a LATERAL join is combining it with set-valued functions that should themselves receive different arguments per row, for example splitting a comma-separated column with unnest in PostgreSQL or STRING_SPLIT in SQL Server. Without LATERAL or CROSS APPLY, the function would have to be called with a fixed argument, with LATERAL every row gets its own, correlated function call.

Parameterized table functions that need a variable number of arguments per row, for example a distance calculation using the coordinates of the respective row as parameters, can also only be expressed cleanly with LATERAL. These cases are rarer than top-N-per-group, but without LATERAL practically impossible to solve in a single SQL statement without falling back to procedural extensions.

-- Splitting a comma-separated column per row using LATERAL and unnest
SELECT p.product_id, p.product_name, tag
FROM products p,
LATERAL unnest(string_to_array(p.tags, ',')) AS tag
WHERE p.tags IS NOT NULL;

-- SQL Server: CROSS APPLY with STRING_SPLIT per row
SELECT p.product_id, p.product_name, tag.value
FROM products p
CROSS APPLY STRING_SPLIT(p.tags, ',') AS tag
WHERE p.tags IS NOT NULL;

6. LATERAL versus the window function approach

The top-N-per-group task can also be solved without a LATERAL join using a window function: ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) in a CTE, followed by a WHERE rn <= 3 in the outer query. Both approaches deliver the same result when written correctly, but differ in execution strategy and readability depending on the situation.

The LATERAL join is usually the clearer choice when N is small and the optimizer can push the LIMIT value directly into the subquery, which often enables an index scan with early termination per group instead of numbering the whole table. The window function approach, in turn, has the advantage when additional window calculations like running totals are needed alongside the ranking, since it already performs a full scan with sorting and can supply that extra information without another join.

-- Same top-3-per-customer result using a window function instead of LATERAL
WITH ranked_orders AS (
    SELECT order_id, customer_id, order_date, amount,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id ORDER BY order_date DESC
           ) AS rn
    FROM orders
)
SELECT customer_id, order_id, order_date, amount
FROM ranked_orders
WHERE rn <= 3
ORDER BY customer_id, order_date DESC;

-- The window function scans and sorts every row in every partition,
-- while the LATERAL version can stop after 3 rows per customer
-- if an index on (customer_id, order_date DESC) exists

7. Performance and the execution plan

The big practical advantage of a LATERAL join at small N values is that the optimizer can exploit the LIMIT clause inside the subquery. With a matching index on (customer_id, order_date DESC), the database only needs to read the first three rows of the index for each customer and can then stop immediately, instead of reading and sorting all of that customer's orders. With thousands of customers each having hundreds of orders, this difference is substantial.

The window function alternative, on the other hand, usually has to read and sort all rows of all groups completely once before the rank filter can be applied, because ROW_NUMBER() is only evaluated after full partitioning and sorting. At large N relative to group size, this difference evens out again, which is why checking EXPLAIN ANALYZE for both variants before a final decision is advisable.

8. Differences across database systems

PostgreSQL has fully supported LATERAL since version 9.3, both explicitly with JOIN LATERAL and implicitly for any subquery in FROM that accesses previous tables of the FROM clause, as long as the keyword is set. MySQL has supported LATERAL since version 8.0.14, with the same basic rules as PostgreSQL. SQL Server has no LATERAL keyword, but offers the same functionality under a different name with CROSS APPLY and OUTER APPLY since SQL Server 2005.

Oracle has supported LATERAL since version 12c and additionally offers CROSS APPLY and OUTER APPLY as alternative syntax, which eases the transition from SQL Server. Important for portable code: anyone switching between PostgreSQL and SQL Server cannot simply replace LATERAL with CROSS APPLY without removing the ON true condition and replacing LIMIT with TOP, but the underlying logic stays identical.

9. Common mistakes with LATERAL joins

The most common mistake is forgetting ON true, or an equivalent condition, after the lateral subquery in PostgreSQL when the actual correlation condition already lives inside the parentheses. Without this condition, the database reports a syntax error, because an explicit JOIN LATERAL formally expects an ON clause, even if it has nothing left to filter content-wise.

A second mistake is confusing JOIN LATERAL with LEFT JOIN LATERAL in reports that must be complete, as described in the section on outer lateral joins. A third mistake is attempting to apply a LATERAL join to a subquery that already performs an aggregation over multiple rows of the outer table itself, which leads to unexpected, usually too-low aggregate values, because the aggregation is computed per row instead of per group.

10. Summary

A LATERAL join solves a problem where ordinary joins and subqueries fail: accessing column values of the current row within a correlated subquery in FROM. That makes top-N-per-group queries, dynamic table function calls, and multi-column, per-row computed results possible to express directly and readably, using the LATERAL keyword in PostgreSQL and MySQL 8, and CROSS APPLY plus OUTER APPLY in SQL Server.

Compared to a window function approach, a LATERAL join is often the more performant choice at small N values, because the optimizer can push the LIMIT into the subquery and use an index scan with early termination. When additional window calculations are needed, the window function variant has the advantage. The choice between JOIN LATERAL and LEFT JOIN LATERAL decides whether rows with no match in the subquery drop out of the result or are kept with NULL values.

11. FAQ: LATERAL Joins