.svg)
.svg)
.svg)
.svg)
.svg)
.svg)
.svg)
Updated and tested 1 October 2026
Microsoft's definition of APPLY, from the FROM clause reference, is one sentence: "Specifies that the right_table_source of the APPLY operator is evaluated against every row of the left_table_source."
Once per row of the left side. That is the whole idea, and a JOIN cannot do it, because a join evaluates its two sides independently and then matches them. With APPLY the right side sees the current left row and uses its column values. Examples below use the schema from the rest of this series: dbo.customers, dbo.orders and dbo.order_items.
CROSS APPLY against OUTER APPLY
Two variants, and the analogy holds precisely. CROSS APPLY behaves like an inner join: no rows on the right for a given left row, and that left row disappears. OUTER APPLY in SQL Server behaves like a left join, keeping the left row and filling the right side's columns with NULLs.
SELECT c.company_name,
latest.order_id,
latest.order_date
FROM dbo.customers AS c
CROSS APPLY
(
SELECT TOP (1) o.order_id, o.order_date
FROM dbo.orders AS o
WHERE o.customer_id = c.customer_id
ORDER BY o.order_date DESC
) AS latest;Run that against a customer table where 40 of 500 companies have never ordered anything and you get 460 rows. The 40 vanish silently. Fine if you wanted customers with a recent order. A bug if you wanted every customer with their last order attached. Swap one word:
SELECT c.company_name,
latest.order_id,
latest.order_date
FROM dbo.customers AS c
OUTER APPLY
(
SELECT TOP (1) o.order_id, o.order_date
FROM dbo.orders AS o
WHERE o.customer_id = c.customer_id
ORDER BY o.order_date DESC
) AS latest;500 rows, with NULL in order_id and order_date for the 40. I have fixed this exact bug in three reporting stacks and the symptom is always the same: a total slightly too low, and nobody can say when it started.
There is no bare APPLY.
Write FROM dbo.customers AS c APPLY (...) AS x and the parser rejects it with an incorrect-syntax error. You pick CROSS or OUTER every time, which is better language design than JOIN quietly meaning INNER JOIN when you leave the word out.
APPLY with a table-valued function
This is the use case Microsoft documents on that page, and most people meet it last. A table-valued function takes a column from the left side as an argument, which a join cannot express.
CREATE OR ALTER FUNCTION dbo.fn_top_order_lines (@order_id int, @how_many int)
RETURNS TABLE
AS
RETURN
(
SELECT TOP (@how_many)
i.product_sku,
i.quantity,
i.unit_price,
i.quantity * i.unit_price AS line_total
FROM dbo.order_items AS i
WHERE i.order_id = @order_id
ORDER BY i.quantity * i.unit_price DESC
);
GO
SELECT o.order_id,
o.total_amount,
lines.product_sku,
lines.line_total
FROM dbo.orders AS o
CROSS APPLY dbo.fn_top_order_lines(o.order_id, 3) AS lines
WHERE o.status = 'shipped';That is an inline table-valued function, meaning the body is a single RETURN (SELECT ...) with no BEGIN block. Inline TVFs get expanded into the calling query and optimised as one unit, so the plan looks much like the equivalent hand-written subquery. Multi-statement TVFs, the kind that declare a table variable and fill it, behave far worse in a plan. Keep function bodies inline when you intend to APPLY them at scale.
Top N rows per group
This is the pattern that sends most people looking for MSSQL CROSS APPLY syntax in the first place. Three most recent orders per customer:
SELECT c.company_name,
recent.order_id,
recent.order_date,
recent.total_amount
FROM dbo.customers AS c
CROSS APPLY
(
SELECT TOP (3) o.order_id, o.order_date, o.total_amount
FROM dbo.orders AS o
WHERE o.customer_id = c.customer_id
ORDER BY o.order_date DESC
) AS recent
ORDER BY c.company_name, recent.order_date DESC;Be clear about what this is. Top-N-per-group with CROSS APPLY is a community idiom and not a documented use case on Microsoft's FROM reference. It works. The performance on your data and your indexes is yours to check.
CROSS APPLY against the ROW_NUMBER alternative
The same result with a window function inside a common table expression:
WITH ranked AS
(
SELECT o.order_id,
o.customer_id,
o.order_date,
o.total_amount,
ROW_NUMBER() OVER (PARTITION BY o.customer_id
ORDER BY o.order_date DESC) AS rn
FROM dbo.orders AS o
)
SELECT c.company_name, r.order_id, r.order_date, r.total_amount
FROM ranked AS r
JOIN dbo.customers AS c ON c.customer_id = r.customer_id
WHERE r.rn <= 3
ORDER BY c.company_name, r.order_date DESC;Both are correct. They perform very differently, and what decides it is how much of the orders table you end up touching.
I think the ROW_NUMBER version is over-taught. It is the answer everyone gives and it reads well. It also has no shortcut available to it. ROW_NUMBER numbers every row in every partition before WHERE rn <= 3 throws most of them away, so it reads the whole orders table however little you asked for. APPLY with an index on (customer_id, order_date DESC) turns each left row into a seek. Three index rows, then it stops. Fifty customers, fifty tiny seeks. On a small driving set that is not a close call, and I reach for APPLY first.
The window function wins when the left side is large, or when you were going to read most of the orders table anyway. One scan and one sort beats two million index seeks. It also handles ties properly if you switch to RANK or DENSE_RANK, which APPLY with TOP cannot do without WITH TIES.
Why the CTE half of that query adds no cost of its own is a longer story, told in common table expressions in SQL Server. Run both versions under SET STATISTICS IO ON and compare logical reads.
