{"id":1379,"date":"2012-12-15T10:00:41","date_gmt":"2012-12-15T15:00:41","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=1379"},"modified":"2014-11-13T13:52:45","modified_gmt":"2014-11-13T18:52:45","slug":"a-join-a-day-right-anti-semi-join","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/1379\/a-join-a-day-right-anti-semi-join\/","title":{"rendered":"A Join A Day \u2013 Right Anti Semi Join"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nThis is the fifteenth 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

\nAs you might have expected, the Right Anti Semi Join is logically equivalent to the Left Anti Semi Join<\/a>, just the roles of the two input row sources have switched. So, a logical right anti semi join query is one that returns all the rows from the right side for which no match on the left side exists.\n<\/p>\n

RIGHT ANTI SEMI JOIN Example<\/h3>\n

\nAs with the Right Semi Join, there is no direct way to write a logical right anti semi join query. However, we again can take the above description literally:\n<\/p>\n

\n[sql]\nSELECT th.*
\nFROM Production.Product p
\nRIGHT OUTER JOIN Production.TransactionHistory th
\n ON p.ProductID = th.ProductID
\nWHERE p.ProductID IS NULL;
\n[\/sql]\n<\/div>\n

\nThe query returns all TransactionHistory records for which the Product table entry does not exist (anymore). Executing it produces an empty result:\n<\/p>\n

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

\nThis however is expected: There is a foreign key relationship defined between these two tables so the above query will always return zero rows. But it still gives a good logical right anti semi join example.\n<\/p>\n

\nLet's also look at the TblA \u2013 TblB<\/span> example:\n<\/p>\n

\n[sql]\nSELECT b.*
\nFROM dbo.TblA AS a
\nRIGHT OUTER JOIN dbo.TblB AS b
\n ON a.TblA_Val = b.TblB_Val
\nWHERE a.TblA_Val IS NULL;
\n[\/sql]\n<\/div>\n

\nThis query follows the same pattern as the previous query. It first finds for all right side rows all potential left side matches and then returns only the right side rows that did not have a left side match. Before we look at the result here is the content of the two tables again:\n<\/p>\n

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

\nRunning this query returns only a single row from TblB<\/span>:\n<\/p>\n

\n\"all-cases<\/a>\n<\/p>\n

\nThe row with TblB_Val = 6<\/span> is the only TblB<\/span> row, that does not have a match in TblA<\/span>.\n<\/p>\n

RIGHT ANTI SEMI JOIN Syntax<\/h3>\n

\nAs with the Right Semi Join, there is no single statement that is equivalent to a logical right anti semi join. Any combination out of JOIN<\/span> and WHERE<\/span> that leads to only rows (and columns) from the right side being returned that do not have a left side match is a logical right anti semi join query and might get optimized using an Anti Semi Join operator.\n<\/p>\n

RIGHT ANTI SEMI JOIN Operator<\/h3>\n

\nToday's first example query, while being a logical anti semi join query does not result in an Anti Semi Join operator:\n<\/p>\n

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

\nIt instead goes through a two-step process again, first using a Right Outer Join operator to find matches and then a Filter operator to remove rows with matches. My experience has been that queries written this way end up with an execution plan like this in most cases. Instead of trying to force the optimizer in one direction, just write the query the most logical way using a NOT EXISTS()<\/span> clause:\n<\/p>\n

\n[sql]\nSELECT *
\nFROM Production.TransactionHistory th
\nWHERE NOT EXISTS ( SELECT 1
\n FROM Production.Product p
\n WHERE p.ProductID = th.ProductID );
\n[\/sql]\n<\/div>\n

\nThis query is switching the order of the two tables to be able to use the NOT EXISTS()<\/span> clause. However, the SQL Server optimizer recognizes based on the row count and row size estimates that for this query it is actually better to us a Right Anti Semi Join operator. So it switches the order of the tables back during compilation:\n<\/p>\n

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

Summary<\/h3>\n

\nThere is no single statement that represents a logical right anti semi join. There are ways to write a query that is equivalent to a right anti semi join however, by using the other join types we have discussed before. If a query meets the anti semi join pattern, SQL Server might decide to use a Right Anti Semi Join operator. For that it might even change the order of the two row sources if it estimates the result to be more efficient.\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":"

The Right Anti Semi Join, like the Right Semi Join, does not have a corresponding T-SQL statement. In the right circumstances however, the SQL Server optimizer will use the Right Anti Semi Join operator to build an efficient plan.<\/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],"tags":[],"class_list":["post-1379","post","type-post","status-publish","format-standard","hentry","category-a-join-a-day","category-fundamentals","category-general","category-series"],"acf":[],"yoast_head":"\nA Join A Day \u2013 Right Anti Semi Join - sqlity.net<\/title>\n<meta name=\"description\" content=\"The Right Anti Semi Join, like the Right Semi Join, does not have a corresponding T-SQL statement. In the right circumstances however, the SQL Server optimizer will use the Right Anti Semi Join operator to build an efficient plan.\" \/>\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\/1379\/a-join-a-day-right-anti-semi-join\/\" \/>\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 Right Anti Semi Join - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"The Right Anti Semi Join, like the Right Semi Join, does not have a corresponding T-SQL statement. In the right circumstances however, the SQL Server optimizer will use the Right Anti Semi Join operator to build an efficient plan.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/1379\/a-join-a-day-right-anti-semi-join\/\" \/>\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-15T15:00:41+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:52:45+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/logical-right-anti-semi-join-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\\\/1379\\\/a-join-a-day-right-anti-semi-join\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1379\\\/a-join-a-day-right-anti-semi-join\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"A Join A Day \u2013 Right Anti Semi Join\",\"datePublished\":\"2012-12-15T15:00:41+00:00\",\"dateModified\":\"2014-11-13T18:52:45+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1379\\\/a-join-a-day-right-anti-semi-join\\\/\"},\"wordCount\":739,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1379\\\/a-join-a-day-right-anti-semi-join\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/logical-right-anti-semi-join-example.jpg\",\"articleSection\":[\"A Join A Day\",\"Fundamentals\",\"General\",\"Series\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1379\\\/a-join-a-day-right-anti-semi-join\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1379\\\/a-join-a-day-right-anti-semi-join\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1379\\\/a-join-a-day-right-anti-semi-join\\\/\",\"name\":\"A Join A Day \u2013 Right Anti Semi Join - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1379\\\/a-join-a-day-right-anti-semi-join\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1379\\\/a-join-a-day-right-anti-semi-join\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/logical-right-anti-semi-join-example.jpg\",\"datePublished\":\"2012-12-15T15:00:41+00:00\",\"dateModified\":\"2014-11-13T18:52:45+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"The Right Anti Semi Join, like the Right Semi Join, does not have a corresponding T-SQL statement. In the right circumstances however, the SQL Server optimizer will use the Right Anti Semi Join operator to build an efficient plan.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1379\\\/a-join-a-day-right-anti-semi-join\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1379\\\/a-join-a-day-right-anti-semi-join\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1379\\\/a-join-a-day-right-anti-semi-join\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/logical-right-anti-semi-join-example.jpg\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/logical-right-anti-semi-join-example.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1379\\\/a-join-a-day-right-anti-semi-join\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"A Join A Day \u2013 Right Anti Semi Join\"}]},{\"@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 Right Anti Semi Join - sqlity.net","description":"The Right Anti Semi Join, like the Right Semi Join, does not have a corresponding T-SQL statement. In the right circumstances however, the SQL Server optimizer will use the Right Anti Semi Join operator to build an efficient plan.","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\/1379\/a-join-a-day-right-anti-semi-join\/","og_locale":"en_US","og_type":"article","og_title":"A Join A Day \u2013 Right Anti Semi Join - sqlity.net","og_description":"The Right Anti Semi Join, like the Right Semi Join, does not have a corresponding T-SQL statement. In the right circumstances however, the SQL Server optimizer will use the Right Anti Semi Join operator to build an efficient plan.","og_url":"https:\/\/sqlity.net\/en\/1379\/a-join-a-day-right-anti-semi-join\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2012-12-15T15:00:41+00:00","article_modified_time":"2014-11-13T18:52:45+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/logical-right-anti-semi-join-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\/1379\/a-join-a-day-right-anti-semi-join\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/1379\/a-join-a-day-right-anti-semi-join\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"A Join A Day \u2013 Right Anti Semi Join","datePublished":"2012-12-15T15:00:41+00:00","dateModified":"2014-11-13T18:52:45+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/1379\/a-join-a-day-right-anti-semi-join\/"},"wordCount":739,"commentCount":0,"image":{"@id":"https:\/\/sqlity.net\/en\/1379\/a-join-a-day-right-anti-semi-join\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/logical-right-anti-semi-join-example.jpg","articleSection":["A Join A Day","Fundamentals","General","Series"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/1379\/a-join-a-day-right-anti-semi-join\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/1379\/a-join-a-day-right-anti-semi-join\/","url":"https:\/\/sqlity.net\/en\/1379\/a-join-a-day-right-anti-semi-join\/","name":"A Join A Day \u2013 Right Anti Semi Join - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/1379\/a-join-a-day-right-anti-semi-join\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/1379\/a-join-a-day-right-anti-semi-join\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/logical-right-anti-semi-join-example.jpg","datePublished":"2012-12-15T15:00:41+00:00","dateModified":"2014-11-13T18:52:45+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"The Right Anti Semi Join, like the Right Semi Join, does not have a corresponding T-SQL statement. In the right circumstances however, the SQL Server optimizer will use the Right Anti Semi Join operator to build an efficient plan.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/1379\/a-join-a-day-right-anti-semi-join\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/1379\/a-join-a-day-right-anti-semi-join\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/1379\/a-join-a-day-right-anti-semi-join\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/logical-right-anti-semi-join-example.jpg","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/logical-right-anti-semi-join-example.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/1379\/a-join-a-day-right-anti-semi-join\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"A Join A Day \u2013 Right Anti Semi Join"}]},{"@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_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-mf","jetpack-related-posts":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1379","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=1379"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1379\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=1379"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=1379"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=1379"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}