OUTER APPLY and CROSS APPLY in SQL Server with Examples

APPLY runs a table expression once per row of the outer table — something a normal join can't express. The canonical use: "top N related rows per parent", which is awkward with joins and natural with APPLY. CROSS APPLY keeps only outer rows that produce results (like INNER JOIN); OUTER APPLY keeps them all (like LEFT JOIN). Every result below is real output from the companion demo database.

The problem joins can't solve

"Show each customer's two biggest orders." A join brings all orders; a TOP 2 in a joined subquery applies globally, not per customer. APPLY evaluates per row:

CROSS APPLY — top N per group

SELECT c.Name, t.OrderDate, t.Amount
FROM Customers c
CROSS APPLY (
    SELECT TOP 2 OrderDate, Amount
    FROM Orders o
    WHERE o.CustomerId = c.CustomerId
    ORDER BY Amount DESC
) t
ORDER BY c.Name, t.Amount DESC;

Name   OrderDate    Amount
-----  ----------   -------
Asha   2026-06-02   1200.00
Asha   2026-06-20    450.00
Meera  2026-07-15    990.00
Meera  2026-06-11    780.00
Rahul  2026-06-05   2300.00

For each customer, the inner query runs with that customer's id — TOP 2 … ORDER BY is evaluated per customer. Note who's missing: John and Sara (no orders) produce empty inner results, and CROSS APPLY drops them — inner-join semantics.

The same question via window functions (a CTE with ROW_NUMBER) is often equivalent; APPLY tends to win when an index on (CustomerId, Amount DESC) exists and the outer table is small relative to the inner — measure both on real data.

OUTER APPLY — keep the rowless parents

Same query, OUTER instead of CROSS, TOP 1 for compactness:

SELECT c.Name, t.Amount
FROM Customers c
OUTER APPLY (
    SELECT TOP 1 Amount
    FROM Orders o
    WHERE o.CustomerId = c.CustomerId
    ORDER BY Amount DESC
) t
ORDER BY c.Name;

Name   Amount
-----  -------
Asha   1200.00
John   NULL
Meera   990.00
Rahul  2300.00
Sara   NULL

All five customers; the orderless ones carry NULL — left-join semantics, per-row evaluation. "Every customer with their largest order, if any" in one readable query.

APPLY with table-valued functions

APPLY is the only way to pass outer-row columns into a table-valued function:

CREATE FUNCTION dbo.OrdersAbove (@CustomerId INT, @MinAmount DECIMAL(10,2))
RETURNS TABLE AS RETURN (
    SELECT OrderId, OrderDate, Amount
    FROM Orders
    WHERE CustomerId = @CustomerId AND Amount >= @MinAmount
);
GO

SELECT c.Name, f.OrderId, f.Amount
FROM Customers c
CROSS APPLY dbo.OrdersAbove(c.CustomerId, 500) f;

A join can't feed c.CustomerId into the function's parameter — APPLY exists precisely for this "correlated table expression" shape. Built-in TVFs work the same way; the everyday example is splitting a delimited column:

SELECT p.ProductId, s.value AS Tag
FROM Products p
CROSS APPLY STRING_SPLIT(p.TagsCsv, ',') s;

APPLY (VALUES …) — computed columns you can reuse

A lesser-known gem: CROSS APPLY (VALUES ...) names an expression once and lets every later clause reference it:

SELECT o.OrderId, v.Net, v.Tax, v.Net + v.Tax AS Gross
FROM Orders o
CROSS APPLY (VALUES (o.Amount / 1.18, o.Amount - o.Amount / 1.18)) v(Net, Tax)
WHERE v.Net > 400;

Without APPLY, Amount / 1.18 gets repeated in the select list and the WHERE clause; with it, the formula lives in one place. The same trick unpivots columns to rows while keeping NULLs — often preferable to UNPIVOT (see pivoting data).

CROSS APPLY vs INNER JOIN — when they're the same

For a plain correlated subquery with no TOP/function, the optimizer usually produces identical plans for CROSS APPLY and INNER JOIN. APPLY earns its place when the inner side:

• uses TOP / ORDER BY per outer row (top-N-per-group),
• is a function taking outer columns as arguments,
• is a VALUES constructor for named expressions,
• contains logic (aggregates + filters) that would be contorted as a join.

Choosing

Performance note: APPLY executes the inner side per outer row conceptually, so it shines when the outer side is selective and the inner side is indexed on the correlation column. Millions of outer rows against an unindexed inner table is where APPLY plans go bad — the fix is the supporting index, same as for any correlated pattern.