What is the difference between CROSS APPLY and OUTER APPLY?

Processing rows in one set using another set is a common coding pattern used mostly with combining or comparing rows from sets. SQL Server offers three set operators: UNION, INTERSECT and EXCEPT, for handling scenario which compares rows from one set to another and completes the return set. In some specific cases, an alternative operator which is APPLY can be used for handling similar scenario. Here is post on APPLY operator;

The APPLY operator not exactly a set operator. It is a table operator which evaluates rows in one set based on an expression set with another set, NOT combining two sets in similar manner used by other set operators but like a JOIN. It is used with FROM clause and just like JOINs, two sets are marked as “left” set and “right” set. The “right” set is always either a table-valued function or a derived table which gets processed for each row returning from the “left” set. The syntax for APPLY is as follows;

SELECT <column list>
FROM <left-table> AS <alias>
APPLY <derived table | table-valued function> AS <alias>

There are two types of APPLY: CROSS APPLY and OUTER APPLY.

CROSS APPLY
CROSS APPLY processes the “right” set for each row found in the “left” set in a similar CROSS-JOIN manner. However, if an empty result is generated by the “right” set for the correlated row given by “left”, the row will NOT be included in the resultset, in a similar INNER-JOIN fashion. Here is an example for CROSS APPLY.

1. USE AdventureWorks2012
2. GO
3.  
4. -- This returns all products
5. -- There are 504 products
6. SELECT ProductID, Name
7. FROM Production.Product
8. ORDER BY ProductID
9. GO
10.  
11. -- Create a table-valued function that returns top orders related to given product
12. CREATE FUNCTION dbo.GetTopOrdersForTheProduct (@ProductId int)
13. RETURNS TABLE
14. AS
15. RETURN
16.     SELECT TOP (2) h.SalesOrderNumber, h.OrderDate, (d.OrderQty * d.UnitPrice) OrderAmount
17.     FROM Sales.SalesOrderHeader h
18.         INNER JOIN Sales.SalesOrderDetail d
19.             ON h.SalesOrderID = d.SalesOrderID
20.     WHERE ProductID = @ProductId
21.     ORDER BY (d.OrderQty * d.UnitPrice) DESC
22. GO
23.  
24. -- check the function
25. -- this does not return any records as there are no order for the product id 1
26. SELECT * FROM dbo.GetTopOrdersForTheProduct (1)
27. -- this returns records as there are orders for the product 707
28. SELECT * FROM dbo.GetTopOrdersForTheProduct (707)
29.  
30. -- Joining SELECT with TVF using CROSS APPLY
31. -- This does not return products like 1, 2
32. SELECT ProductID, Name, o.SalesOrderNumber, o.OrderDate, o.OrderAmount
33. FROM Production.Product
34.     CROSS APPLY
35.         dbo.GetTopOrdersForTheProduct (ProductID) o
36. ORDER BY ProductID

OUTER APPLY
The behavior of OUTER APPLY is same as CROSS APPLY except one which is the only difference between CROSS APPLY and OUTER APPLY. In the presence of an empty result from “right” set, CROSS APPLY excludes the row found in “left” from the returned result but OUTER APPLY includes it. This behavior is conceptually similar to LEFT OUTER JOIN. Here is the code describing it;

1. -- Joining SELECT with TVF using CROSS APPLY
2. -- This returns all from Product table
3. SELECT ProductID, Name, o.SalesOrderNumber, o.OrderDate, o.OrderAmount
4. FROM Production.Product
5.     OUTER APPLY
6.         dbo.GetTopOrdersForTheProduct (ProductID) o
7. ORDER BY ProductID