{"id":27615,"date":"2022-12-25T11:40:32","date_gmt":"2022-12-25T06:10:32","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-alternative-for-full-outer-join-in-indexed-view\/"},"modified":"2022-12-25T11:40:32","modified_gmt":"2022-12-25T06:10:32","slug":"solved-alternative-for-full-outer-join-in-indexed-view","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-alternative-for-full-outer-join-in-indexed-view\/","title":{"rendered":"[Solved] Alternative for full outer join in indexed view"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-37166361\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"37166361\" data-parentid=\"37157878\" data-score=\"3\" data-position-on-page=\"1\" data-highest-scored=\"1\" data-question-has-accepted-highest-score=\"1\" itemprop=\"acceptedAnswer\" itemscope itemtype=\"https:\/\/schema.org\/Answer\">\n<div class=\"post-layout\">\n<div class=\"votecell post-layout--left\"><\/div>\n<div class=\"answercell post-layout--right\">\n<div class=\"s-prose js-post-body\" itemprop=\"text\">\n<p>There is a <a rel=\"nofollow noopener\" target=\"_blank\" href=\"http:\/\/www.sqlservercentral.com\/articles\/Indexing\/indexedviewswithouterjoins\/1884\/\">workaround<\/a> for indexed views with &#8217;emulated outer joins&#8217;. However, it&#8217;s hideous and I wouldn&#8217;t advocate it. <\/p>\n<p>A better solution would be to index the underlying tables and skip the whole view. Or create two indexed views and do the <code>outer join<\/code> in the query calling the views. <\/p>\n<p>Yet another solution is to not create an indexed view, but an actual table (which you can keep up-to-date with triggers) and put indexes on that. Since you&#8217;re materializing the view anyway, this might be the best solution.<\/p>\n<p>In all likelihood, an indexed view (whichever method you choose) won&#8217;t give you the (performance) result you are looking for (though the last method where you create an actual table might). Can you explain why you feel you need an indexed view in this particular case?<\/p>\n<p>By the way, <a rel=\"nofollow noopener\" target=\"_blank\" href=\"http:\/\/blog.sqlauthority.com\/2010\/06\/29\/sql-server-outer-join-not-allowed-in-indexed-views\/\">here<\/a> is a (short) explanation <strong>why<\/strong> indexed views don&#8217;t allow <code>outer joins<\/code>.<\/p>\n<\/p><\/div>\n<div class=\"mt24\"><\/div>\n<\/div>\n<p>            <span class=\"d-none\" itemprop=\"commentCount\">4<\/span> <\/p><\/div>\n<\/div>\n<p>[ad_2]<\/p>\n<p>solved Alternative for full outer join in indexed view <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] There is a workaround for indexed views with &#8217;emulated outer joins&#8217;. However, it&#8217;s hideous and I wouldn&#8217;t advocate it. A better solution would be to index the underlying tables and skip the whole view. Or create two indexed views and do the outer join in the query calling the views. Yet another solution is &#8230; <a title=\"[Solved] Alternative for full outer join in indexed view\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-alternative-for-full-outer-join-in-indexed-view\/\" aria-label=\"More on [Solved] Alternative for full outer join in indexed view\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[320],"tags":[534,341,500],"class_list":["post-27615","post","type-post","status-publish","format-standard","hentry","category-solved","tag-join","tag-sql","tag-sql-server"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>[Solved] Alternative for full outer join in indexed view - JassWeb<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/jassweb.com\/solved\/solved-alternative-for-full-outer-join-in-indexed-view\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] Alternative for full outer join in indexed view - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] There is a workaround for indexed views with &#8217;emulated outer joins&#8217;. However, it&#8217;s hideous and I wouldn&#8217;t advocate it. A better solution would be to index the underlying tables and skip the whole view. Or create two indexed views and do the outer join in the query calling the views. Yet another solution is ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-alternative-for-full-outer-join-in-indexed-view\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-12-25T06:10:32+00:00\" \/>\n<meta name=\"author\" content=\"Kirat\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirat\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-alternative-for-full-outer-join-in-indexed-view\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-alternative-for-full-outer-join-in-indexed-view\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] Alternative for full outer join in indexed view\",\"datePublished\":\"2022-12-25T06:10:32+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-alternative-for-full-outer-join-in-indexed-view\/\"},\"wordCount\":170,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"join\",\"sql\",\"sql-server\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-alternative-for-full-outer-join-in-indexed-view\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-alternative-for-full-outer-join-in-indexed-view\/\",\"name\":\"[Solved] Alternative for full outer join in indexed view - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2022-12-25T06:10:32+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-alternative-for-full-outer-join-in-indexed-view\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-alternative-for-full-outer-join-in-indexed-view\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-alternative-for-full-outer-join-in-indexed-view\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] Alternative for full outer join in indexed view\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/jassweb.com\/solved\/#website\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"name\":\"JassWeb\",\"description\":\"Build High-quality Websites\",\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/jassweb.com\/solved\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\",\"name\":\"Jass Web\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"contentUrl\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"width\":693,\"height\":132,\"caption\":\"Jass Web\"},\"image\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\",\"name\":\"Kirat\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750\",\"contentUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\/\/jassweb.com\"],\"url\":\"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] Alternative for full outer join in indexed view - JassWeb","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:\/\/jassweb.com\/solved\/solved-alternative-for-full-outer-join-in-indexed-view\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] Alternative for full outer join in indexed view - JassWeb","og_description":"[ad_1] There is a workaround for indexed views with &#8217;emulated outer joins&#8217;. However, it&#8217;s hideous and I wouldn&#8217;t advocate it. A better solution would be to index the underlying tables and skip the whole view. Or create two indexed views and do the outer join in the query calling the views. Yet another solution is ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-alternative-for-full-outer-join-in-indexed-view\/","og_site_name":"JassWeb","article_published_time":"2022-12-25T06:10:32+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-alternative-for-full-outer-join-in-indexed-view\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-alternative-for-full-outer-join-in-indexed-view\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] Alternative for full outer join in indexed view","datePublished":"2022-12-25T06:10:32+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-alternative-for-full-outer-join-in-indexed-view\/"},"wordCount":170,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["join","sql","sql-server"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-alternative-for-full-outer-join-in-indexed-view\/","url":"https:\/\/jassweb.com\/solved\/solved-alternative-for-full-outer-join-in-indexed-view\/","name":"[Solved] Alternative for full outer join in indexed view - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-12-25T06:10:32+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-alternative-for-full-outer-join-in-indexed-view\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-alternative-for-full-outer-join-in-indexed-view\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-alternative-for-full-outer-join-in-indexed-view\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] Alternative for full outer join in indexed view"}]},{"@type":"WebSite","@id":"https:\/\/jassweb.com\/solved\/#website","url":"https:\/\/jassweb.com\/solved\/","name":"JassWeb","description":"Build High-quality Websites","publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jassweb.com\/solved\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/jassweb.com\/solved\/#organization","name":"Jass Web","url":"https:\/\/jassweb.com\/solved\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/","url":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","contentUrl":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","width":693,"height":132,"caption":"Jass Web"},"image":{"@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31","name":"Kirat","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750","caption":"Kirat"},"sameAs":["http:\/\/jassweb.com"],"url":"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/"}]}},"_links":{"self":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/27615","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/comments?post=27615"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/27615\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=27615"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=27615"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=27615"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}