What is CROSS APPLY / OUTER APPLY?
Quick Answer
APPLY joins a table to a table-valued expression that's evaluated per outer row and can reference the outer row's columns. CROSS APPLY is like an inner join (drops outer rows with no result); OUTER APPLY is like a left join (keeps them with NULLs). Use it for “top N per group”, calling a TVF per row, or expanding STRING_SPLIT/JSON per row.
APPLY is like a join where the right side is a subquery or function that runs once per outer row and can use that row's values. CROSS APPLY behaves like an inner join, OUTER APPLY like a left join. The killer use case is top-N-per-group: for each customer, CROSS APPLY a SELECT TOP 3 ... ORDER BY correlated to that customer — you can't do that cleanly with a plain join because the join's right side can't see the left row. It's also how you call a table-valued function for every row.
- Top 3 orders per customer:
FROM Customers c CROSS APPLY (SELECT TOP 3 * FROM Orders o WHERE o.CustomerId = c.Id ORDER BY o.Total DESC) x. - Only
APPLY(notJOIN) can reference outer columns inside a subquery'sTOP/ORDER BYor pass them to a function. - Often compiles to a nested loops join; fine for moderate row counts, watch it on very large outer sets.