{"id":5731,"date":"2016-10-23T17:55:47","date_gmt":"2016-10-23T12:25:47","guid":{"rendered":"http:\/\/sqlhints.com\/?p=5731"},"modified":"2019-04-25T07:41:21","modified_gmt":"2019-04-25T02:11:21","slug":"outer-apply-in-sql-server","status":"publish","type":"post","link":"https:\/\/sqlhints.com\/2016\/10\/23\/outer-apply-in-sql-server\/","title":{"rendered":"OUTER APPLY in Sql Server"},"content":{"rendered":"

OUTER APPLY operator in Sql Server returns all rows from the LEFT table expression of the OUTER APPLY operator irrespective of whether it produces the corresponding result in the RIGHT table expression or not. The RIGHT table expression columns value will be NULL in the final result for those rows in the LEFT table expression that don\u2019t produce the result from the RIGHT table expression. So from the result perspective we can say that the OUTER APPLY is similar to the classic LEFT OUTER JOIN<\/strong><\/p>\n

To understand OUTER APPLY in Sql Server with extensive list of examples, let us create the demo tables with sample data as shown in the following image by executing the following script:<\/p>\n

\"apply-operator-demonstration-tables\"<\/a><\/p>\n

\r\nCREATE DATABASE SqlHintsOUTERAPPLYDemo\r\nGO\r\nUSE SqlHintsOUTERAPPLYDemo\r\nGO\r\n--Create Customers Table and Insert records\r\nCREATE TABLE Customers \r\n( CustomerId INT, Name VARCHAR(50) )\r\nGO\r\nINSERT INTO Customers(CustomerId, Name) \r\nVALUES(1,'Shree'), (2,'Kalpana'), (3,'Basavaraj')\r\nGO\r\n--Create Orders Table and Insert records into it\r\nCREATE TABLE Orders\r\n(OrderId INT, CustomerId INT,Amount MONEY, OrderDate DATETIME)\r\nGO\r\nINSERT INTO Orders(OrderId, CustomerId,Amount,OrderDate) \r\nVALUES(100,1,100.0,Getdate()-30), (101,1,200.0,Getdate()-20),\r\n      (103,1,300.0,Getdate()-10), (104,2,150.0,Getdate())\t\r\nGO\r\n<\/pre>\n
Create a Table Valued Function<\/strong><\/p>\n
Now execute the following script to create an inline Table valued function fnGetLastTwoCustomerOrders, which returns order details of the last 2 orders corresponding to the input customer id.<\/p>\n
\r\nCREATE FUNCTION dbo.fnGetLastTwoCustomerOrders\r\n( @CustomerId AS INT )  \r\nRETURNS TABLE \r\nAS \r\nRETURN ( \r\n\t\tSELECT TOP 2 * \r\n\t\tFROM Orders O \r\n\t\tWHERE O.CustomerId = @CustomerId \r\n\t\tORDER BY OrderDate DESC\r\n\t)\r\n<\/pre>\n
Let us verify this functions result for the customer id = 1<\/p>\n
\r\n--Return last two order details for the customer id = 1\r\nSELECT * FROM dbo.fnGetLastTwoCustomerOrders(1) O\r\nGO\r\n<\/pre>\n
RESULT:<\/strong>
\n\"apply-operator-table-valued-function-result\"<\/a><\/p>\n
OUTER APPLY Examples<\/h3>\n
Example 1: Demonstrates how we can re-write a very basic LEFT OUTER JOIN query by OUTER APPLY operator<\/h3>\n
OUTER APPLY is not an alternative for LEFT OUTER JOIN, instead each of them have their own pros and cons. The general rule of thumb is you may like to use the OUTER JOIN if you want to evaluate\/execute the RIGHT side table expression or Table Valued function for each row in the LEFT side table expression. Just to demonstrates how we can use OUTER APPLY operator with a very basic example, here in this example will re-write a very basic LEFT OUTER JOIN query by OUTER APPLY.<\/p>\n
We can write a query like below by using LEFT OUTER JOIN to get all Customers table rows irrespective of whether a corresponding record exists in the Orders table or not. For the records in the Customers table which don’t have matching records in the Orders table, the Orders table columns in the result will have NULL values<\/p>\n
\r\nSELECT *\r\nFROM Customers C\r\n        LEFT OUTER JOIN Orders O\r\n\t   ON C.CustomerId = O.CustomerId\r\n<\/pre>\n
RESULT<\/strong>
\n\"left-outer-join-query\"<\/a><\/p>\n
We can re-write the above LEFT OUTER JOIN query using OUTER APPLY as shown below and still get the same result<\/p>\n
\r\nSELECT *\r\nFROM Customers C\r\n  OUTER APPLY (SELECT * \r\n               FROM Orders O\r\n     \t       WHERE O.CustomerId = C.CustomerId) ORD\r\n<\/pre>\n
RESULT<\/strong>
\n\"outer-apply-left-outer-join-equivalent\"<\/a><\/p>\n
From the above results we can see that the re-written OUTER APPLY query is returning the same result as that of the LEFT OUTER JOIN query.<\/p>\n
[ALSO READ] CROSS APPLY in Sql Server<\/a><\/h3>\n
Example 2: OUTER APPLY operator between a Table and user defined Table Valued Function<\/h3>\n
We can write a query like below using OUTER APPLY operator to get details of all the customers with their last two order details if it is present. This query basically evaluates the function fnGetLastTwoCustomerOrders for each row in the Customer table by taking CustomerId as the input.<\/p>\n
\r\nSELECT *\r\nFROM dbo.Customers C\r\n      OUTER APPLY \r\n        dbo.fnGetLastTwoCustomerOrders(C.CustomerId) O\r\n<\/pre>\n
RESULT:<\/strong>
\n\"sql-server-outer-apply-example-1\"<\/a><\/p>\n
From the result we can see that for the customer with customer id =1, we have two orders in the result even though this customer has three orders because the function fnGetLastTwoCustomerOrders returns at max the last two orders for any given CustomerId. And for CustomerId =2, we see only one order detail in the result as this customer has placed only one order till now and for the Customer with CustomerId = 3 in the result we can see only the Customer details and all the Order details columns value for this customer is NULL because this customer is yet to place any order.<\/p>\n
[ALSO] <\/p>\n
[ALSO READ] Sql Server APPLY operator<\/a><\/h3>\n
Example 3: OUTER APPLY operator between a Table and Table Expression<\/h3>\n
In the example 2 the RIGHT side of the OUTER APPLY operator was User Defined Function, this example demonstrates how we can have a Table Expression on the right side of this operator.<\/p>\n
The below script can also achieve the same result as the example 2, but here we have not used the Table Valued Function fnGetLastTwoCustomerOrders instead we have used the derived table.<\/p>\n
\r\nSELECT *\r\nFROM dbo.Customers C\r\n\tOUTER APPLY (SELECT TOP 2 * \r\n\t  \t     FROM Orders O \r\n\t\t     WHERE O.CustomerId = C.CustomerId \r\n\t\t     ORDER BY OrderDate DESC) ORD\r\n<\/pre>\n
RESULT:<\/strong>
\n\"sql-server-outer-apply-example-2\"<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
OUTER APPLY operator in Sql Server returns all rows from the LEFT table expression of the OUTER APPLY operator irrespective of whether it produces the corresponding result in the RIGHT table expression or not. The RIGHT table expression columns value will be NULL in the final result for those rows in the LEFT table expression … Continue reading OUTER APPLY in Sql Server<\/span> →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[3],"tags":[1437,1438,1450,1446,1447,1448,1449,321,1451,986,1452],"class_list":["post-5731","post","type-post","status-publish","format-standard","hentry","category-sql-server","tag-apply-in-sql","tag-apply-operator-in-sql","tag-examples-of-outer-apply","tag-outer-apply","tag-outer-apply-examples","tag-outer-apply-in-sql","tag-outer-apply-in-sql-server","tag-sql","tag-sql-outer-apply","tag-sql-server","tag-sql-server-outer-apply"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p3xNAz-1ur","_links":{"self":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/5731","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/comments?post=5731"}],"version-history":[{"count":17,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/5731\/revisions"}],"predecessor-version":[{"id":5773,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/5731\/revisions\/5773"}],"wp:attachment":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/media?parent=5731"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/categories?post=5731"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/tags?post=5731"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}