PrismitaLEARN · PRACTICE · GROW

What are CROSS APPLY and OUTER APPLY?

SQL Server T-SQL Advanced 3–5 Yrs5+ Yrs SQL Query 1 min read

Quick Answer

APPLY joins a table to a table expression that can reference columns from the outer row — a correlated join. CROSS APPLY is like an inner join (drops outer rows with no match); OUTER APPLY is like a left join (keeps them with NULLs). It's SQL Server's version of a lateral join.

APPLY lets me join to a subquery or a table-valued function that can reference the current outer row — a correlated join. CROSS APPLY behaves like an inner join and OUTER APPLY like a left join. The two things I use it for constantly are top-N per group — the last three orders for each customer, where I put a TOP 3 ... ORDER BY inside the APPLY — and calling a table-valued function once per row. A plain JOIN can't do either because its right side is evaluated independently of the left.

  • Top-N per group: SELECT c.Name, x.* FROM Customers c CROSS APPLY (SELECT TOP 3 * FROM Orders o WHERE o.CustomerId = c.CustomerId ORDER BY o.OrderDate DESC) x;
  • Calling a table-valued function per row: ... CROSS APPLY dbo.SplitString(t.Csv, ',').
  • A regular JOIN can't do this because its right side can't see the left row.
  • OUTER APPLY keeps customers who have no orders.

SQL — last 3 orders per customer 
SELECT c.Name, o.OrderId, o.OrderDate
FROM   dbo.Customers c
CROSS APPLY (
    SELECT TOP 3 OrderId, OrderDate
    FROM   dbo.Orders o
    WHERE  o.CustomerId = c.CustomerId
    ORDER  BY o.OrderDate DESC
) o;
All SQL Server questions More on T-SQL