Saturday, February 15, 2014

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 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
  4. -- This returns all products
  5. -- There are 504 products
  6. SELECT ProductID, Name
  7. FROM Production.Product
  8. ORDER BY ProductID
  9. GO
  11. -- Create a table-valued function that returns top orders related to given product
  12. CREATE FUNCTION dbo.GetTopOrdersForTheProduct (@ProductId int)
  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
  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)
  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


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


No comments: