SpDev Install free

CROSS APPLY and OUTER APPLY in SQL Server, with practical examples

What APPLY does that a JOIN can't: top N rows per group, calling table-valued functions per row, unpivoting columns and reusing calculations. With reads compared against ROW_NUMBER.

By Areeb Ahmer, . 5 minute read.

APPLY runs a table expression once for each row of the table on its left, and the expression can use that row’s columns. A JOIN can’t do that: both sides of a join are independent, and they meet only in the ON clause.

  • CROSS APPLY keeps the left row only if the right side returns at least one row, like an inner join.
  • OUTER APPLY keeps every left row, with NULLs when the right side returns nothing, like a left join.

The examples run against the SpDevDemo database.

Top N rows per group

The classic use: each customer’s three most recent orders.

SELECT c.CustomerId, c.Name, o.OrderId, o.OrderDate, o.Total
FROM dbo.Customers AS c
CROSS APPLY (
    SELECT TOP (3) OrderId, OrderDate, Total
    FROM dbo.Orders
    WHERE CustomerId = c.CustomerId          -- uses the outer row: not possible in a JOIN
    ORDER BY OrderDate DESC, OrderId DESC
) AS o
WHERE c.Country = 'PK' AND c.CustomerId <= 20;

For each Pakistani customer among the first 20, the inner query runs with that customer’s id and returns at most three orders. A customer without orders disappears; with OUTER APPLY, they’d appear once with NULL order columns.

APPLY or ROW_NUMBER?

The same result with a window function:

WITH ranked AS (
    SELECT CustomerId, OrderId, OrderDate, Total,
           ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate DESC, OrderId DESC) AS rn
    FROM dbo.Orders
)
SELECT c.CustomerId, c.Name, r.OrderId, r.OrderDate, r.Total
FROM dbo.Customers AS c
JOIN ranked AS r ON r.CustomerId = c.CustomerId AND r.rn <= 3
WHERE c.Country = 'PK' AND c.CustomerId <= 20;

Which is faster depends on how many groups you want. Give both an index that matches the inner query, then compare them for a few customers chosen through a join, the German customers who signed up in four days of March 2026:

CREATE INDEX IX_Orders_Customer_Date ON dbo.Orders (CustomerId, OrderDate DESC, OrderId DESC) INCLUDE (Total);

SET STATISTICS IO ON;

SELECT c.CustomerId, o.OrderId, o.Total
FROM dbo.Customers AS c
CROSS APPLY (SELECT TOP (3) OrderId, Total FROM dbo.Orders
             WHERE CustomerId = c.CustomerId ORDER BY OrderDate DESC, OrderId DESC) AS o
WHERE c.Country = 'DE' AND c.CreatedAt >= '2026-03-01' AND c.CreatedAt < '2026-03-05';

WITH ranked AS (
    SELECT CustomerId, OrderId, Total,
           ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate DESC, OrderId DESC) AS rn
    FROM dbo.Orders
)
SELECT c.CustomerId, r.OrderId, r.Total
FROM dbo.Customers AS c
JOIN ranked AS r ON r.CustomerId = c.CustomerId AND r.rn <= 3
WHERE c.Country = 'DE' AND c.CreatedAt >= '2026-03-01' AND c.CreatedAt < '2026-03-05';

SET STATISTICS IO OFF;
-- CROSS APPLY: one seek per customer, 14 customers
Table 'Orders'. Scan count 14, logical reads 42
-- ROW_NUMBER: numbers every order of every customer, then keeps 3 each for the 14
Table 'Orders'. Scan count 1, logical reads 655

Now ask for the latest three orders of all 20,000 customers, and it reverses:

-- CROSS APPLY: 20,000 seeks
Table 'Orders'. Scan count 20000, logical reads 63978
-- ROW_NUMBER: one ordered scan of the index
Table 'Orders'. Scan count 1, logical reads 655

The rule: APPLY wins when you want a few groups out of many, and the window function wins when you want most of them. When the filter is on the partitioning column itself (WHERE CustomerId = 1), SQL Server pushes it into the window query and both read only what they need; the difference appears when the groups are chosen through a join, as here.

Calling a table-valued function per row

APPLY is how you pass a column to a table-valued function:

CREATE OR ALTER FUNCTION dbo.OrderLinesOf (@OrderId INT)
RETURNS TABLE
AS
RETURN
    SELECT l.ProductId, p.Name, l.Quantity, l.Quantity * l.UnitPrice AS LineTotal
    FROM dbo.OrderLines AS l
    JOIN dbo.Products AS p ON p.ProductId = l.ProductId
    WHERE l.OrderId = @OrderId;
GO

SELECT o.OrderNumber, x.Name, x.Quantity, x.LineTotal
FROM dbo.Orders AS o
CROSS APPLY dbo.OrderLinesOf(o.OrderId) AS x
WHERE o.OrderId BETWEEN 1 AND 3;

An inline function like this one is expanded into the query, so this costs the same as writing the join yourself.

Unpivoting columns into rows

CROSS APPLY with a VALUES list turns columns into rows, more flexibly than UNPIVOT (it keeps NULLs and handles several columns at once):

SELECT c.CustomerId, v.Attribute, v.Value
FROM dbo.Customers AS c
CROSS APPLY (VALUES
    (N'Name',    c.Name),
    (N'Email',   CAST(c.Email AS NVARCHAR(200))),
    (N'Country', CAST(c.Country AS NVARCHAR(200)))
) AS v (Attribute, Value)
WHERE c.CustomerId = 42;
CustomerId  Attribute  Value
----------  ---------  ----------------------
42          Name       Customer 42
42          Email      [email protected]
42          Country    DE

Naming a calculation once

CROSS APPLY with a single-row SELECT gives a calculation a name you can use in WHERE, ORDER BY and later calculations, without repeating the expression:

SELECT TOP (5) o.OrderId, t.Net, t.Tax, t.Net + t.Tax AS Gross
FROM dbo.Orders AS o
JOIN dbo.Customers AS c ON c.CustomerId = o.CustomerId
CROSS APPLY (SELECT o.Total AS Net,
                    o.Total * CASE c.Country WHEN 'GB' THEN 0.20 WHEN 'DE' THEN 0.19 ELSE 0 END AS Tax) AS t
WHERE t.Tax > 500
ORDER BY t.Tax DESC;

The optimizer folds it into the query: there is no per-row cost.

OUTER APPLY: keep the rows with nothing on the right

The latest order of each customer, including customers who have none:

SELECT TOP (3) c.CustomerId, c.Name, last_order.OrderDate
FROM dbo.Customers AS c
OUTER APPLY (
    SELECT TOP (1) OrderDate FROM dbo.Orders
    WHERE CustomerId = c.CustomerId
    ORDER BY OrderDate DESC
) AS last_order
ORDER BY c.CustomerId;

Things to know

  • APPLY is T-SQL; the SQL standard’s equivalent is LATERAL, which SQL Server doesn’t support.
  • With a multi-statement table-valued function on the right, the function really runs once per row. With an inline function or a subquery, it’s optimized as one query.
  • Without an index that supports the inner query, “once per row” means a scan per row. Check the plan’s executions on the inner side.

SpDev Pro reports operators that run thousands of times inside a profiled procedure. An APPLY whose inner side scans a table once per outer row is one of the patterns it shows, with the number of executions and the reads they cost.