{"id":3767,"date":"2013-01-05T22:12:22","date_gmt":"2013-01-05T22:12:22","guid":{"rendered":"http:\/\/craftydba.com\/?p=3767"},"modified":"2013-01-06T16:52:33","modified_gmt":"2013-01-06T16:52:33","slug":"cross-apply-operator","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=3767","title":{"rendered":"Cross Apply Operator"},"content":{"rendered":"

\"\"<\/a>The SELECT<\/a> reserved word is part of the Data Manipulation Language (DML) defined by Codd and is used to query data from the database. Because of its popularity, there are many clauses and functions that can be used to construct a query to retrieve data.<\/p>\n

Today, I am continuing our exploration of this reserved word by reviewing queries that merge data using the APPLY operator. The APPLY<\/a> operator come in two flavors: CROSS and OUTER. I am going to concentrate on the former during this discussion.<\/p>\n

I will be using the Adventure Works<\/a> 2012 sample database supplied by Microsoft during this talk. The SQL snippet below uses a simple GROUP BY clause to determine the distribution of Sales Orders by year.<\/p>\n

-- Use Adventure Works\r\nUSE [AdventureWorks2012]\r\nGO\r\n\r\n-- Break down of orders per year using group by - (4 rows)\r\nSELECT \r\n    YEAR(DueDate) as 'Year'\r\n  , COUNT(*) as 'Total'\r\nFROM\r\n    [Sales].[SalesOrderHeader]\r\nGROUP BY\r\n    YEAR(DueDate)\r\nORDER BY \r\n    YEAR(DueDate)\r\nGO\r\n\r\n<\/span><\/pre>\n
 Let us start our exploration off by getting the business requirements for the query from the Sales & Marketing manager.  (S)he noticed that the order date posts two days late in our home grown ERP<\/a> system.  We need to adjust the current summary query to fix this date issue.<\/p>\n
The example below is really cool.  Think of the CROSS APPLY operator as a INNER JOIN with a correlated sub-query.  For added clarity, we can even make it look like a table value function by supplying a column alias at the table alias level.<\/p>\n
-- Order date posts 2 days later, adjust the output - (211 rows)\r\nSELECT \r\n    Adjusted.DateValue,\r\n    COUNT(SH1.SalesOrderID) as TotalOrders,\r\n    SUM(SH1.TotalDue) as TotalDue\r\nFROM \r\n    [Sales].[SalesOrderHeader] AS SH1 INNER JOIN \r\n    [Sales].[SalesOrderDetail] AS SO1 ON SH1.SalesOrderID = SO1.SalesOrderID\r\n    CROSS APPLY\r\n    (\r\n\tSELECT CONVERT(DATE, DATEADD(DAY, -2, SH1.OrderDate))\r\n    ) AS Adjusted (DateValue)\r\nWHERE\r\n    YEAR(Adjusted.DateValue) = 2008\r\nGROUP BY\r\n    Adjusted.DateValue\r\nORDER BY\r\n    Adjusted.DateValue\r\nGO\r\n\r\n<\/span><\/pre>\n
The code below produces the same result as the one above.  In fact, the query plans are exactly the same.  However, the example above is cleaner and self documenting.  We are using a adjusted date. <\/p>\n
-- Old school way to write query\r\nSELECT \r\n    CONVERT(DATE, DATEADD(DAY, -2, SH1.OrderDate)) as DateValue,\r\n    COUNT(SH1.SalesOrderID) as TotalOrders,\r\n    SUM(SH1.TotalDue) as TotalDue\r\nFROM \r\n    [Sales].[SalesOrderHeader] AS SH1 INNER JOIN \r\n    [Sales].[SalesOrderDetail] AS SO1 ON SH1.SalesOrderID = SO1.SalesOrderID\r\nWHERE\r\n    YEAR(DATEADD(DAY, -2, SH1.OrderDate)) = 2008\r\nGROUP BY\r\n    CONVERT(DATE, DATEADD(DAY, -2, SH1.OrderDate))\r\nORDER BY\r\n    CONVERT(DATE, DATEADD(DAY, -2, SH1.OrderDate))\r\nGO\r\n\r\n<\/span><\/pre>\n
I want to thank Paul White<\/a> for debating this with me on a forum post<\/a>.  That is how we learn things.<\/p>\n
Last but not least, you will see the CROSS APPLY operator used mostly with Dynamic Management Functions which are table value functions.  <\/p>\n
The snippet below does a sample call to the [ufnGetContactInformation] function for a single record.  Then, it cross applies the function against all orders in Australia in a SELECT statement.<\/p>\n
-- Use the built in TVF for Adventure Works\r\nSELECT * FROM [dbo].[ufnGetContactInformation] (1209)\r\nGO\r\n\r\n-- Get orders by country, customer, # orders, $ orders - (3582 rows)\r\nSELECT\r\n    ST.Name as Country,\r\n    CI.LastName,\r\n    CI.FirstName,\r\n    COUNT(SH.SalesOrderID) as TotalOrders,\r\n    SUM(SH.TotalDue) as TotalDue\r\nFROM \r\n    [Sales].[SalesOrderHeader] SH \r\n    INNER JOIN [Sales].[Customer] AS C\r\n\tON  SH.CustomerID = C.CustomerID\r\n    INNER JOIN [Sales].[SalesTerritory] AS ST\r\n\tON C.TerritoryID = ST.TerritoryID \r\n    CROSS APPLY [dbo].[ufnGetContactInformation] (C.PersonID) AS CI\r\nWHERE\r\n    C.StoreID IS NULL and ST.Name = 'Australia'\r\nGROUP BY\r\n    ST.Name,\r\n    CI.LastName,\r\n    CI.FirstName\r\n\r\n<\/pre>\n
<\/span><\/p>\n
In a nutshell, the CROSS APPLY operator is kinda mandatory when using the Dynamic Management Functions in a query. <\/p>\n
However, there are other uses for this construct.  I showed you one example in which a calculation would have been coded all over the place in the aggregation query.  By using the CROSS APPLY, we made the query more readable and simplistic.  <\/p>\n
If you have a correlated sub query in the future, think about using this construct.  Check out Rob Farley’s article<\/a> and his thoughts on the subject.<\/p>\n
Next time I will be talking about the OUTER APPLY operator.<\/p>\n
Cross Apply Operator Example<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
The SELECT reserved word is part of the Data Manipulation Language (DML) defined by Codd and is used to query data from the database. Because of its popularity, there are many clauses and functions that can be used to construct a query to retrieve data. Today, I am continuing our exploration of this reserved word by reviewing queries that merge data using the APPLY operator. The APPLY operator come in two flavors: CROSS and OUTER. I am going to concentrate on the former during this discussion. I will be using…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[115,702,31,705,706,12,82,15,37,28,29],"class_list":["post-3767","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-correlated-sub-query","tag-cross-apply","tag-database-developer","tag-dmf","tag-dynamic-management-functions","tag-free-code","tag-inner-join","tag-john-f-miner-iii","tag-select","tag-sql-server","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/3767","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3767"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/3767\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3767"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3767"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3767"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}