By Kalman Toth, M.Phil., M.Phil., MCDBA, MCITP: DBA, Developer, BI Developer

October 28 , 2007

The terminology used with CROSS APPLY and OUTER APPLY is somewhat misleading. A better name for CROSS APPLY would be INNER APPLY. Here is how they behave.

This is the code sample to create a function for "APPLY":

USE AdventureWorks;
GO

CREATE FUNCTION Sales.fnCustomerOrderTotal ( @CustomerId int)
returns @Result table (OrderTotal money)
as
BEGIN
INSERT @Result
SELECT sum(sod.LineTotal) as OrderTotal
FROM Sales.SalesOrderHeader as soh
JOIN Sales.SalesOrderDetail as sod
on sod.salesOrderId = soh.SalesOrderId
WHERE soh.CustomerId = @CustomerId
GROUP by soh.CustomerId
RETURN
END
GO

OUTER APPLY will return rows also for customers without order as nulls. In the example below null is translated into 0.0. This is logical business wise.

SELECT Store=s.Name,
OrderTotal=convert(varchar,isnull(cot.OrderTotal,0.0),1)
FROM Sales.Store as s
JOIN Sales.Customer as c
ON s.CustomerID = c.CustomerID
OUTER APPLY Sales.fnCustomerOrderTotal(c.customerId) as cot
ORDER BY Store

This is the partial result set:

This is the way to use "CROSS APPLY". The result set will not contain 0.0 (null) orders.

SELECT Store=s.Name,
OrderTotal=convert(varchar,isnull(cot.OrderTotal,0.0),1)
FROM Sales.Store as s
JOIN Sales.Customer as c
ON s.CustomerID = c.CustomerID
CROSS APPLY Sales.fnCustomerOrderTotal(c.customerId) as cot
ORDER BY Store
GO

This is the partial result set: