Microsoft® SQL Server 2012 Unleashed

OUTER APPLY

OUTER APPLY returns from the outer table both rows that produce a result set and rows that do not. Rows that do not produce a result set from the table-valued function return NULL values in the columns produced by the table-valued function.

The following example is similar to the query in Listing 45.45 but replaces CROSS APPLY with the OUTER APPLY clause:

Click here to view code image

SELECT P.PRoductID, p.Name, s.salesOrderID, s.ORderQtyFROM Production.Product POuter APPLY dbo.fn_GetTopSales(p.ProductID, 3) AS sWHERE p.ProductID BETWEEN 820 AND 823ORDER BY p.ProductID, s.OrderQty DESCGO/* outputPRoductID Name                           salesOrderID ORderQty--------- ----------------------------- ------------ --------      820 HL Road ...