{"id":1275,"date":"2012-12-09T10:00:56","date_gmt":"2012-12-09T15:00:56","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=1275"},"modified":"2014-11-13T13:53:19","modified_gmt":"2014-11-13T18:53:19","slug":"a-join-a-day-the-outer-apply","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/1275\/a-join-a-day-the-outer-apply\/","title":{"rendered":"A Join A Day \u2013 The Outer Apply"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nThis is the ninth post in my A Join A Day<\/a> series about SQL Server Joins. Make sure to let me know how I am doing or ask your burning join related questions by leaving a comment below.\n<\/p>\n

\nThe OUTER APPLY<\/span> command is an extension to the CROSS APPLY<\/span> command (see A Join A Day \u2013 The Cross Apply<\/a>) that allows us – just like a left outer join – to include non-matched rows from the left side in the result of a query.\n<\/p>\n

OUTER APPLY Example<\/h3>\n

\nThe example I used in yesterday's post called the ufnGetContactInformation<\/span> for each row in the person table. A person in the AdventureWorks2008R2 database is considered a business entity and there is a BusinessEntity<\/span> table that includes all persons as well as a few other entities. The above function when used on a non-person entity does not return a row.\n<\/p>\n

\nLet's assume we want to use the BusinessEntity<\/span> table in a query and return the contact information for all entities that are indeed a person. But we also want a row for each non-person in the result. That is just the functionality that the OUTER APPLY<\/span> statement can provide:\n<\/p>\n

\n[sql]\nSELECT be.BusinessEntityID, be.ModifiedDate,
\n ci.FirstName, ci.LastName, ci.JobTitle, ci.BusinessEntityType
\nFROM Person.BusinessEntity AS be
\nOUTER APPLY dbo.ufnGetContactInformation(be.BusinessEntityID) AS ci;
\n[\/sql]\n<\/div>\n

\nThe result of the above query includes all 19683 persons as well as all other entities resulting in a total of 20777 returned rows.\n<\/p>\n

\n\"Example<\/a>\n<\/p>\n

\nThe right side columns of all rows for which the right side did not actually return a row, are valued NULL. This is the same behavior that we have seen with the left outer join (see A Join A Day \u2013 The Left Outer Join<\/a>).\n<\/p>\n

\nJust for completeness let's look at yesterday's second example again as well, replacing the CROSS APPLY<\/span> with an OUTER APPLY<\/span>. Remember, the XML_data<\/span> table contains these rows:\n<\/p>\n

\n\"Setup<\/a>\n<\/p>\n

\nThe cross apply query did not return any row for Id = 4<\/span>. So let's see what happens when using an outer apply instead.\n<\/p>\n

\n[sql]\nSELECT x.Id, x.val, n.b_node.value('@name', 'NVARCHAR(MAX)') b_node_name
\nFROM dbo.XML_data x
\nOUTER APPLY x.val.nodes('\/\/b') n(b_node);
\n[\/sql]\n<\/div>\n

\nThis query returns the same 6 rows for Id<\/span> 1, 2 and 3 as yesterday's cross apply query. However, as we expected, it returns one additional row for Id = 4<\/span>.\n<\/p>\n

\n\"Complete<\/a>\n<\/p>\n

OUTER APPLY Syntax<\/h3>\n

\nThe only syntax difference between an outer apply and a cross apply statement is the OUTER<\/span> keyword. Everything else stays the same. That means that the order of the two row sources is important for the outer apply too, as the second row source can reference attributes of the first row source.\n<\/p>\n

\nOther than with outer joins, in an outer apply the OUTER<\/span> keyword is not optional. So the syntax is always Table1 OUTER APPLY Table2<\/span> where Table1 and Table2 can be tables or other valid row sources.\n<\/p>\n

OUTER APPLY Operator<\/h3>\n

\nThe outer apply is handled by the nested loops operator and like the cross apply does not have its own. However, as you might have guessed, it is not the Nested Loops (Inner Join) operator that is responsible for cross apply queries. Instead it is the Nested Loops (Left Outer Join) operator:\n<\/p>\n

\n\"outer<\/a>\n<\/p>\n

\nNoteworthy is that it is always a left outer join operator and not a full or even right outer join. But that makes sense, as the right side is always executed in the context of the current left side row. That means that there cannot be a row on the right side without a matching current row on the left side.\n<\/p>\n

Summary<\/h3>\n

\nOuter apply and cross apply are very similar in that they both allow the right side to reference attributes of the left side. They both query the right side for each left side row. Each left side row is joined with all rows resulting from its execution of the right side. The only difference is that the outer apply also returns left side rows for which the right side row source returned an empty result set.\n<\/p>\n

A Join A Day<\/h3>\n

\nThis post is part of my December 2012 \"A Join A Day\" blog post series. You can find the table of contents with all posts published so far in the introductory post: A Join A Day \u2013 Introduction<\/a>. Check back there frequently throughout the month.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

This is day nine of the A Join A Day series and we are going to look at the outer apply statement. We will discover the differences to the cross apply and the similarities to the left outer join. Read on to get all the details.<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[28,29,5,27,23],"tags":[],"class_list":["post-1275","post","type-post","status-publish","format-standard","hentry","category-a-join-a-day","category-fundamentals","category-general","category-series","category-t-sql-statements"],"acf":[],"yoast_head":"\nA Join A Day \u2013 The Outer Apply - sqlity.net<\/title>\n<meta name=\"description\" content=\"This is day nine of the A Join A Day series and we are going to look at the outer apply statement. We will discover the differences to the cross apply and the similarities to the left outer join. Read on to get all the details.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sqlity.net\/en\/1275\/a-join-a-day-the-outer-apply\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"A Join A Day \u2013 The Outer Apply - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"This is day nine of the A Join A Day series and we are going to look at the outer apply statement. We will discover the differences to the cross apply and the similarities to the left outer join. Read on to get all the details.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/1275\/a-join-a-day-the-outer-apply\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2012-12-09T15:00:56+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:53:19+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/outer-apply-example.jpg\" \/>\n<meta name=\"author\" content=\"Sebastian Meine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sebastian Meine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1275\\\/a-join-a-day-the-outer-apply\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1275\\\/a-join-a-day-the-outer-apply\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"A Join A Day \u2013 The Outer Apply\",\"datePublished\":\"2012-12-09T15:00:56+00:00\",\"dateModified\":\"2014-11-13T18:53:19+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1275\\\/a-join-a-day-the-outer-apply\\\/\"},\"wordCount\":767,\"commentCount\":1,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1275\\\/a-join-a-day-the-outer-apply\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/outer-apply-example.jpg\",\"articleSection\":[\"A Join A Day\",\"Fundamentals\",\"General\",\"Series\",\"T-SQL Statements\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1275\\\/a-join-a-day-the-outer-apply\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1275\\\/a-join-a-day-the-outer-apply\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1275\\\/a-join-a-day-the-outer-apply\\\/\",\"name\":\"A Join A Day \u2013 The Outer Apply - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1275\\\/a-join-a-day-the-outer-apply\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1275\\\/a-join-a-day-the-outer-apply\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/outer-apply-example.jpg\",\"datePublished\":\"2012-12-09T15:00:56+00:00\",\"dateModified\":\"2014-11-13T18:53:19+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"This is day nine of the A Join A Day series and we are going to look at the outer apply statement. We will discover the differences to the cross apply and the similarities to the left outer join. Read on to get all the details.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1275\\\/a-join-a-day-the-outer-apply\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1275\\\/a-join-a-day-the-outer-apply\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1275\\\/a-join-a-day-the-outer-apply\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/outer-apply-example.jpg\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/outer-apply-example.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1275\\\/a-join-a-day-the-outer-apply\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"A Join A Day \u2013 The Outer Apply\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\",\"name\":\"Sebastian Meine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"caption\":\"Sebastian Meine\"},\"sameAs\":[\"http:\\\/\\\/sqlity.net\",\"https:\\\/\\\/x.com\\\/sqlity\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"A Join A Day \u2013 The Outer Apply - sqlity.net","description":"This is day nine of the A Join A Day series and we are going to look at the outer apply statement. We will discover the differences to the cross apply and the similarities to the left outer join. Read on to get all the details.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/sqlity.net\/en\/1275\/a-join-a-day-the-outer-apply\/","og_locale":"en_US","og_type":"article","og_title":"A Join A Day \u2013 The Outer Apply - sqlity.net","og_description":"This is day nine of the A Join A Day series and we are going to look at the outer apply statement. We will discover the differences to the cross apply and the similarities to the left outer join. Read on to get all the details.","og_url":"https:\/\/sqlity.net\/en\/1275\/a-join-a-day-the-outer-apply\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2012-12-09T15:00:56+00:00","article_modified_time":"2014-11-13T18:53:19+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/outer-apply-example.jpg","type":"","width":"","height":""}],"author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/1275\/a-join-a-day-the-outer-apply\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/1275\/a-join-a-day-the-outer-apply\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"A Join A Day \u2013 The Outer Apply","datePublished":"2012-12-09T15:00:56+00:00","dateModified":"2014-11-13T18:53:19+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/1275\/a-join-a-day-the-outer-apply\/"},"wordCount":767,"commentCount":1,"image":{"@id":"https:\/\/sqlity.net\/en\/1275\/a-join-a-day-the-outer-apply\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/outer-apply-example.jpg","articleSection":["A Join A Day","Fundamentals","General","Series","T-SQL Statements"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/1275\/a-join-a-day-the-outer-apply\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/1275\/a-join-a-day-the-outer-apply\/","url":"https:\/\/sqlity.net\/en\/1275\/a-join-a-day-the-outer-apply\/","name":"A Join A Day \u2013 The Outer Apply - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/1275\/a-join-a-day-the-outer-apply\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/1275\/a-join-a-day-the-outer-apply\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/outer-apply-example.jpg","datePublished":"2012-12-09T15:00:56+00:00","dateModified":"2014-11-13T18:53:19+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"This is day nine of the A Join A Day series and we are going to look at the outer apply statement. We will discover the differences to the cross apply and the similarities to the left outer join. Read on to get all the details.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/1275\/a-join-a-day-the-outer-apply\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/1275\/a-join-a-day-the-outer-apply\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/1275\/a-join-a-day-the-outer-apply\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/outer-apply-example.jpg","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/outer-apply-example.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/1275\/a-join-a-day-the-outer-apply\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"A Join A Day \u2013 The Outer Apply"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c","name":"Sebastian Meine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","caption":"Sebastian Meine"},"sameAs":["http:\/\/sqlity.net","https:\/\/x.com\/sqlity"]}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-kz","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1275","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=1275"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1275\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=1275"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=1275"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=1275"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}