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

Introduction<\/h3>\n

\nThis is the thirteenth 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 Right Semi Join is the exact mirror image of the Left Semi Join: For every row from the left side, matching rows from the right side are returned. No columns from the left side are returned and rows from the right are returned each at most once.\n<\/p>\n

RIGHT SEMI JOIN Example<\/h3>\n

\nTo write a query with a logical left semi join we could use the EXISTS()<\/span> clause. However, there is not really a concept or statement that would result in a logical right semi join. But we can take the description above literally. That will lead us to a query like this:\n<\/p>\n

\n[sql]\nSELECT DISTINCT be.*
\nFROM Person.BusinessEntityAddress AS bea
\nJOIN Person.BusinessEntity AS be
\n ON bea.BusinessEntityID = be.BusinessEntityID;
\n[\/sql]\n<\/div>\n

\nThe above query produces the following result. It is the same result that we got from yesterday's EXISTS()<\/span> query<\/a>.\n<\/p>\n

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

\nFor this query pattern to be a true right semi join the right side columns must include a primary or unique key. Otherwise the DISTINCT<\/span> might combine multiple separate right side rows into one.\n<\/p>\n

\nThe tables in the TblA \u2013 TblB<\/span> example I have been using do not have a primary key. To make this example work we have to select only distinct values from the left join column before we join to the right side:\n<\/p>\n

\n[sql]\nSELECT b.*
\nFROM (SELECT DISTINCT TblA_Val FROM dbo.TblA) AS a
\nJOIN dbo.TblB AS b
\nON a.TblA_Val = b.TblB_Val;
\n[\/sql]\n<\/div>\n

\nRemember, the two tables in this example contain the following rows:\n<\/p>\n

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

\nRunning the above query leads to this result:\n<\/p>\n

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

\nOnly rows from TblB<\/span> for which a match in TblA<\/span> exists are included. Each TblB<\/span> row is included at most once, even if it has multiple matches in TblA<\/span>.\n<\/p>\n

\nLike the Left Semi Join, the Right Semi Join does not eliminate duplicates. So if a row on the right exists more than once, and if a match on the left exists, all of those rows will be included in the final result.\n<\/p>\n

RIGHT SEMI JOIN Syntax<\/h3>\n

\nAs the previous section suggested there is no statement that is logically equivalent to a right semi join, so there is no syntax to discuss here. There are many ways to write a logical right semi join using the different join commands we have discussed before. However, SQL Servers optimizer might decide to not use a Right Semi Join operator for any of those queries.\n<\/p>\n

RIGHT SEMI JOIN Operator<\/h3>\n

\nAny join query that fits the logical requirements by returning only rows from one of the two sides, each at most once might end up with a Semi Join operator. This operator can be either a Left Semi Join or a Right Semi Join operator. Even in an EXISTS()<\/span> query SQL Server might decide that it is better to flip the order of the tables and use a Right Semi Join operator. However, as with the Left Semi Join, there are many possible execution plans for a logical right semi join query that do not use a Right Semi Join operator and SQL Server might pick any of those for a given logical right semi join query.\n<\/p>\n

\nThe execution plan of the address example above actually does not contain a Right Semi Join operator:\n<\/p>\n

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

\nInstead our left dominant SQL Server optimizer again decided to flip the order of the tables on us and use a Left Semi Join operator instead. To get SQL Server to use a Right Semi Join operator we have to \"pull a fast one\":\n<\/p>\n

\n[sql]\nSELECT DISTINCT be.*
\nFROM Person.BusinessEntityAddress AS bea
\nJOIN Person.BusinessEntity AS be
\n ON bea.BusinessEntityID = be.BusinessEntityID
\nOPTION ( HASH JOIN );
\n[\/sql]\n<\/div>\n

\nThe OPTION(HASH JOIN)<\/span> is a \"hint\" that instructs SQL Server to use the hash join algorithm. This hint clearly has nothing to do with semi joins, it just restricts the optimizer's options enough for it to pick the Right Semi Join operator as new winner for this query:\n<\/p>\n

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

\nI am not going to explain this hint further here. Join hints are topic of day 18 of this series.\n<\/p>\n

Summary<\/h3>\n

\nThe Right Semi Join and the Left Semi Join are the same logical operation. They just switch the roles of the left side row source and the right side row source. The Right Semi Join returns all rows from the right side that have a match on the left side, but each one of them is returned at most once.\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 semi join works like the left semi join, it just switches the role of the two sides. A Right Semi Join returns only rows from the right side base on the existence of matching rows on the left.<\/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-1354","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 The Right Semi Join - sqlity.net<\/title>\n<meta name=\"description\" content=\"The right semi join works like the left semi join, it just switches the role of the two sides. A Right Semi Join returns only rows from the right side base on the existence of matching rows on the left.\" \/>\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\/1354\/a-join-a-day-the-right-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 The Right Semi Join - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"The right semi join works like the left semi join, it just switches the role of the two sides. A Right Semi Join returns only rows from the right side base on the existence of matching rows on the left.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/1354\/a-join-a-day-the-right-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-13T15:00:33+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:52:59+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/logical-right-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\\\/1354\\\/a-join-a-day-the-right-semi-join\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1354\\\/a-join-a-day-the-right-semi-join\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"A Join A Day \u2013 The Right Semi Join\",\"datePublished\":\"2012-12-13T15:00:33+00:00\",\"dateModified\":\"2014-11-13T18:52:59+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1354\\\/a-join-a-day-the-right-semi-join\\\/\"},\"wordCount\":851,\"commentCount\":1,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1354\\\/a-join-a-day-the-right-semi-join\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/logical-right-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\\\/1354\\\/a-join-a-day-the-right-semi-join\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1354\\\/a-join-a-day-the-right-semi-join\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1354\\\/a-join-a-day-the-right-semi-join\\\/\",\"name\":\"A Join A Day \u2013 The Right Semi Join - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1354\\\/a-join-a-day-the-right-semi-join\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1354\\\/a-join-a-day-the-right-semi-join\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/logical-right-semi-join-example.jpg\",\"datePublished\":\"2012-12-13T15:00:33+00:00\",\"dateModified\":\"2014-11-13T18:52:59+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"The right semi join works like the left semi join, it just switches the role of the two sides. A Right Semi Join returns only rows from the right side base on the existence of matching rows on the left.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1354\\\/a-join-a-day-the-right-semi-join\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1354\\\/a-join-a-day-the-right-semi-join\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1354\\\/a-join-a-day-the-right-semi-join\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/logical-right-semi-join-example.jpg\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/logical-right-semi-join-example.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1354\\\/a-join-a-day-the-right-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 The Right 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 The Right Semi Join - sqlity.net","description":"The right semi join works like the left semi join, it just switches the role of the two sides. A Right Semi Join returns only rows from the right side base on the existence of matching rows on the left.","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\/1354\/a-join-a-day-the-right-semi-join\/","og_locale":"en_US","og_type":"article","og_title":"A Join A Day \u2013 The Right Semi Join - sqlity.net","og_description":"The right semi join works like the left semi join, it just switches the role of the two sides. A Right Semi Join returns only rows from the right side base on the existence of matching rows on the left.","og_url":"https:\/\/sqlity.net\/en\/1354\/a-join-a-day-the-right-semi-join\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2012-12-13T15:00:33+00:00","article_modified_time":"2014-11-13T18:52:59+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/logical-right-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\/1354\/a-join-a-day-the-right-semi-join\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/1354\/a-join-a-day-the-right-semi-join\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"A Join A Day \u2013 The Right Semi Join","datePublished":"2012-12-13T15:00:33+00:00","dateModified":"2014-11-13T18:52:59+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/1354\/a-join-a-day-the-right-semi-join\/"},"wordCount":851,"commentCount":1,"image":{"@id":"https:\/\/sqlity.net\/en\/1354\/a-join-a-day-the-right-semi-join\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/logical-right-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\/1354\/a-join-a-day-the-right-semi-join\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/1354\/a-join-a-day-the-right-semi-join\/","url":"https:\/\/sqlity.net\/en\/1354\/a-join-a-day-the-right-semi-join\/","name":"A Join A Day \u2013 The Right Semi Join - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/1354\/a-join-a-day-the-right-semi-join\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/1354\/a-join-a-day-the-right-semi-join\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/logical-right-semi-join-example.jpg","datePublished":"2012-12-13T15:00:33+00:00","dateModified":"2014-11-13T18:52:59+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"The right semi join works like the left semi join, it just switches the role of the two sides. A Right Semi Join returns only rows from the right side base on the existence of matching rows on the left.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/1354\/a-join-a-day-the-right-semi-join\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/1354\/a-join-a-day-the-right-semi-join\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/1354\/a-join-a-day-the-right-semi-join\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/logical-right-semi-join-example.jpg","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/logical-right-semi-join-example.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/1354\/a-join-a-day-the-right-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 The Right 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_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-lQ","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1354","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=1354"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1354\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=1354"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=1354"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=1354"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}