{"id":3796,"date":"2013-01-06T16:17:36","date_gmt":"2013-01-06T16:17:36","guid":{"rendered":"http:\/\/craftydba.com\/?p=3796"},"modified":"2013-01-06T16:52:04","modified_gmt":"2013-01-06T16:52:04","slug":"outer-apply-operator","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=3796","title":{"rendered":"Outer 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 later during this discussion.<\/p>\n

I will be using the Adventure Works<\/a> 2012 sample database supplied by Microsoft during this talk.<\/p>\n

-- Use Adventure Works\r\nUSE [AdventureWorks2012]\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 wants to see if item color influences a customers choice of the product.  I will be using the multiline table value function, [ufnColor2RgbValues], that I created in a previous blog<\/a>.<\/p>\n
Since both the CROSS and OUTER APPLY operators are very similar in nature, I want to compare and contrast their properties.  <\/p>\n
Think of the CROSS APPLY operator as a INNER JOIN with a correlated sub-query.  The TSQL code below returns 30 rows.  However, 14 rows were dropped from the result set due to them not having a color defined.  <\/p>\n
-- Cross Apply same as inner join, drops 14 records - (30 rows)\r\nSELECT\r\n      PC1.Name as MainCategory\r\n    , PS1.Name as SubCategory\r\n    , RGB.Name as ItemColor\r\n    , RGB.HexCode as HexCode\r\n    , SUM(Quantity) as SumItemStock\r\n    , AVG(P1.ListPrice) as AvgItemList\r\n    , AVG(P1.StandardCost) as AvgItemCost\r\nFROM\r\n    [Production].[ProductInventory] AS PI1\r\n    LEFT JOIN [Production].[Product] AS P1\r\n        ON PI1.ProductID = P1.ProductID\r\n    LEFT JOIN [Production].[ProductSubcategory] AS PS1\r\n\tON P1.ProductSubcategoryID = PS1.ProductSubcategoryID\r\n    LEFT JOIN [Production].[ProductCategory] as PC1\r\n\tON PS1.ProductCategoryID = PC1.ProductCategoryID\r\n    CROSS APPLY [dbo].[ufnColor2RgbValues] (P1.Color) AS RGB\r\nWHERE\r\n    PC1.Name IS NOT NULL\r\nGROUP BY\r\n    PC1.Name \r\n  , PS1.Name\r\n  , RGB.Name \r\n  , RGB.HexCode\r\nGO\r\n\r\n<\/span><\/pre>\n
On the other hand, think of the OUTER APPLY operator as a LEFT JOIN with a correlated sub-query.  Because of this property,  all 44 rows of data are returned in the result set.<\/p>\n
-- Out Apply same as left outer join - (44 rows)\r\nSELECT\r\n      PC1.Name as MainCategory\r\n    , PS1.Name as SubCategory\r\n    , RGB.Name as ItemColor\r\n    , RGB.HexCode as HexCode\r\n    , SUM(Quantity) as SumItemStock\r\n    , AVG(P1.ListPrice) as AvgItemList\r\n    , AVG(P1.StandardCost) as AvgItemCost\r\nFROM\r\n    [Production].[ProductInventory] AS PI1\r\n    LEFT JOIN [Production].[Product] AS P1\r\n        ON PI1.ProductID = P1.ProductID\r\n    LEFT JOIN [Production].[ProductSubcategory] AS PS1\r\n\tON P1.ProductSubcategoryID = PS1.ProductSubcategoryID\r\n    LEFT JOIN [Production].[ProductCategory] as PC1\r\n\tON PS1.ProductCategoryID = PC1.ProductCategoryID\r\n    OUTER APPLY [dbo].[ufnColor2RgbValues] (P1.Color) AS RGB\r\nWHERE\r\n    PC1.Name IS NOT NULL\r\nGROUP BY\r\n    PC1.Name \r\n  , PS1.Name\r\n  , RGB.Name \r\n  , RGB.HexCode\r\n GO\r\n\r\n<\/span><\/pre>\n
In a nutshell, the CROSS APPLY and OUTER APPLY operators are like two fraternal <\/a>twins.  Both have the common characteristic of joining data to a sub-query or table valued function.  Nonetheless, they do not merge data the same way.  This characteristic makes them not identical.  One operator does a INNER JOIN and the other does a LEFT JOIN.<\/p>\n
If you have a correlated sub query in the future, think about using these constructs.  Check out Rob Farley’s article<\/a> and his thoughts on the subject.<\/p>\n
Next time I will be talking about the GROUP SETS which is part of my Summarizing Data Series.<\/p>\n
Cross Apply Operator Output<\/a><\/p>\n
Outer Apply Operator Output<\/a><\/p>\n
Outer 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 later 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,12,82,15,83,703,37,28,704,29],"class_list":["post-3796","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-correlated-sub-query","tag-cross-apply","tag-database-developer","tag-dmf","tag-free-code","tag-inner-join","tag-john-f-miner-iii","tag-left-join","tag-outer-apply","tag-select","tag-sql-server","tag-table-valued-function","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/3796","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=3796"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/3796\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3796"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3796"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3796"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}