Home/Learn/SQL/LATERAL JOIN / CROSS APPLY

LATERAL JOIN / CROSS APPLY

Advanced

Subqueries & CTEs

A LATERAL join allows a subquery in the FROM clause to reference columns from preceding table expressions, enabling row-dependent subqueries such as top-N per group.

Overview

Normally a subquery in the FROM clause is independent of the outer query. The LATERAL keyword (PostgreSQL, standard SQL) or CROSS APPLY / OUTER APPLY (SQL Server, Oracle) removes this restriction: the subquery on the right of LATERAL can reference columns from tables to its left, effectively executing once per row of the left table. This makes LATERAL the SQL equivalent of a "for each row, run this subquery" loop. The canonical use case is top-N per group (e.g., the 3 most recent orders per user) without the subquery or window function workaround. LATERAL JOIN matches CROSS JOIN semantics (drops left rows with zero matches); OUTER APPLY / LEFT JOIN LATERAL preserves left rows even when the subquery returns nothing.

Top-N Per Group with LATERAL

The LATERAL subquery receives each user row from the left side and returns the top 3 orders for that specific user. Without LATERAL this requires a window function or a correlated subquery.

SQL — LATERAL top-3 orders per user, LEFT JOIN LATERAL

-- Top 3 orders by amount per user using LATERAL (PostgreSQL)
SELECT
    u.id        AS user_id,
    u.name,
    top_orders.id        AS order_id,
    top_orders.amount,
    top_orders.created_at
FROM users u
JOIN LATERAL (
    SELECT id, amount, created_at
    FROM orders o
    WHERE o.user_id = u.id          -- references left-side column u.id
    ORDER BY amount DESC
    LIMIT 3
) top_orders ON TRUE;               -- ON TRUE: always join (no filter condition)
-- For each user, the subquery runs once and returns at most 3 rows.

-- LEFT JOIN LATERAL: preserve users who have no orders at all
SELECT u.id, u.name, top_orders.amount
FROM users u
LEFT JOIN LATERAL (
    SELECT id, amount
    FROM orders o
    WHERE o.user_id = u.id
    ORDER BY amount DESC
    LIMIT 3
) top_orders ON TRUE;
-- Users with no orders appear with NULL amount (not dropped).

LATERAL vs Window Function — Comparison

A window function with ROW_NUMBER() is often more readable for top-N per group. LATERAL is superior when the inner query has a LIMIT or needs to call a set-returning function.

SQL — LATERAL vs window ROW_NUMBER, CROSS APPLY equivalent

-- Window function approach (often equivalent, sometimes preferred):
SELECT user_id, id AS order_id, amount
FROM (
    SELECT
        o.user_id,
        o.id,
        o.amount,
        ROW_NUMBER() OVER (PARTITION BY o.user_id ORDER BY o.amount DESC) AS rn
    FROM orders o
) ranked
WHERE rn <= 3;

-- LATERAL is preferred when:
-- 1. You need to call a set-returning function per row (PostgreSQL specific):
SELECT u.id, func_result.value
FROM users u
JOIN LATERAL some_set_returning_function(u.id) func_result ON TRUE;

-- 2. You need a true LIMIT inside the subquery (cleaner than ROW_NUMBER filter):
-- LATERAL LIMIT 3 is pushed into the index scan; ROW_NUMBER scans all rows first.

-- SQL Server / Oracle equivalent: CROSS APPLY
SELECT u.id, u.name, recent.id, recent.amount
FROM users u
CROSS APPLY (
    SELECT TOP 3 id, amount
    FROM orders o
    WHERE o.user_id = u.id
    ORDER BY amount DESC
) recent;

Key Points to Remember

1LATERAL (PostgreSQL/standard SQL) and CROSS APPLY (SQL Server) allow a FROM subquery to reference columns from tables to its left.
2The subquery executes once per row of the left table — making it the right tool for top-N per group with LIMIT.
3JOIN LATERAL drops left rows that produce zero subquery rows; LEFT JOIN LATERAL preserves them (like OUTER APPLY).
4Window function ROW_NUMBER() is often equivalent but scans all rows before filtering; LATERAL LIMIT can stop early.
5LATERAL is also used to call set-returning functions per row in PostgreSQL.
6ON TRUE is the conventional join condition when no filtering beyond the subquery's WHERE is needed.

Interview Questions

What is a LATERAL join and when would you use it instead of a window function?

HardGoogle

Write a query to return the 3 most recent orders per user using LATERAL.

HardAmazon

What is the difference between JOIN LATERAL and LEFT JOIN LATERAL?

MediumNetflix

What is the SQL Server equivalent of LATERAL and how does OUTER APPLY differ from CROSS APPLY?

MediumMicrosoft

Ask Aria about LATERAL JOIN / CROSS APPLY

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

Subquery vs JOIN vs CTE — When to Use Which

Derived Tables