{"id":1560,"date":"2012-12-28T10:00:53","date_gmt":"2012-12-28T15:00:53","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=1560"},"modified":"2014-11-13T13:49:49","modified_gmt":"2014-11-13T18:49:49","slug":"a-join-a-day-hash-join-limitations","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/1560\/a-join-a-day-hash-join-limitations\/","title":{"rendered":"A Join A Day \u2013 Hash Join Limitations"},"content":{"rendered":"
\n

Introduction<\/h3>\n

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

\nThere is one remaining join algorithm to dissect a little further: Today we are going to look at which situations can and which situations cannot be handled by the Hash Join operator.\n<\/p>\n

Hash Join<\/h3>\n

\nFor the Hash Join algorithm we again are going to look at all nine logical joins, each as equi- and as nonequi-join. The following is a list of example queries for each combination together with their execution plans, similar to the lists for the Loop Join algorithm<\/a> and the Merge Join algorithm<\/a>. Here too the list will only contain plain equi-join or nonequi-join scenarios and not include mixed cases, as those are handled similarly to equi-joins.\n<\/p>\n

Cross Hash Join<\/h4>\n

\n\"cross-hash-join\"<\/a><\/p>\n

Inner Hash Equi-Join<\/h4>\n

\"inner-hash-equi-join\"<\/a><\/p>\n

Left Hash Equi-Join<\/h4>\n

\"left-hash-equi-join\"<\/a> <\/p>\n

Right Hash Equi-Join<\/h4>\n

\"right-hash-equi-join\"<\/a><\/p>\n

Full Hash Equi-Join<\/h4>\n

\"full-hash-equi-join\"<\/a><\/p>\n

Left Semi Hash Equi-Join<\/h4>\n

\"left-semi-hash-equi-join\"<\/a>\n<\/p>\n

Right Semi Hash Equi-Join<\/h4>\n

\n\"right-semi-hash-equi-join\"<\/a>\n<\/p>\n

\nThis query and the Right Anti Semi Hash Equi-Join query are using the tables that we created in the article about the Loop Join algorithm<\/a>.\n<\/p>\n

Left Anti Semi Hash Equi-Join<\/h4>\n

\n\"left-anti-semi-hash-equi-join\"<\/a>\n<\/p>\n

Right Anti Semi Hash Equi-Join<\/h4>\n

\n\"right-anti-semi-hash-equi-join\"<\/a>\n<\/p>\n

Inner Hash Nonequi-Join<\/h4>\n

\n\"inner-hash-nonequi-join\"<\/a>\n<\/p>\n

Left Hash Nonequi-Join<\/h4>\n

\n\"left-hash-nonequi-join\"<\/a>\n<\/p>\n

Right Hash Nonequi-Join<\/h4>\n

\n\"right-hash-nonequi-join\"<\/a>\n<\/p>\n

Full Hash Nonequi-Join<\/h4>\n

\n\"full-hash-nonequi-join\"<\/a>\n<\/p>\n

Left\/Right Semi Hash Nonequi-Join<\/h4>\n

\n\"semi-hash-nonequi-join\"<\/a>\n<\/p>\n

Left\/Right Anti Semi Hash Nonequi-Join<\/h4>\n

\n\"anti-semi-hash-nonequi-join\"<\/a>\n<\/p>\n

Observations<\/h3>\n

\nThe Hash Join algorithm first builds a hash index for the left side input. For each row a hash value is calculated with a hash function. As we have seen in the article about the Hash Join algorithm<\/a>, a good hash function needs to evenly distribute the rows across all available buckets. To achieve that a hash function is usually derived from a cryptographic function. Because of that, any sense of order gets lost when applying the function. That means that while you can compare two hash values with each other, the result of that comparison will be able to tell you only one thing: If the hash values do not match then the rows do not match either. In particular this means for an inequality comparison like \"greater than\" a hash index is of no value. So we would expect to see hash nonequi-joins to not be possible. The above example queries confirm that for all nonequi-joins including the join-condition-free cross join.\n<\/p>\n

\nThere are no real surprises in the examples above. Every equi-join condition can be handled by the Hash Join operator. Keep in mind however, that any \"left\" type query needs to keep track of all rows in the hash table that have been successfully matched to be able to retrieve the non-matched rows afterwards. This mark requires additional memory. While this is not a lot of addition memory, it might just be enough to cause the hash table not to fit into memory anymore causing the more expensive grace join algorithm<\/a> to be used. So if you change a query from \"inner join\" to \"outer join\" and performance suffers significantly even though both queries use the Hash Join algorithm, this might just be the reason.\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":"

Which logical join types can be conquered by the Hash Join algorithm? Are there any special cases I need to be aware of for which the Hash Join operator might not be the best fit? Read on to get the answers.<\/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,5,27,14],"tags":[],"class_list":["post-1560","post","type-post","status-publish","format-standard","hentry","category-a-join-a-day","category-general","category-series","category-sql-server-internals"],"acf":[],"yoast_head":"\nA Join A Day \u2013 Hash Join Limitations - sqlity.net<\/title>\n<meta name=\"description\" content=\"Which logical join types can be conquered by the Hash Join algorithm? Are there any special cases I need to be aware of for which the Hash Join operator might not be the best fit? Read on to get the answers.\" \/>\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\/1560\/a-join-a-day-hash-join-limitations\/\" \/>\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 Hash Join Limitations - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"Which logical join types can be conquered by the Hash Join algorithm? Are there any special cases I need to be aware of for which the Hash Join operator might not be the best fit? Read on to get the answers.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/1560\/a-join-a-day-hash-join-limitations\/\" \/>\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-28T15:00:53+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:49:49+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/cross-hash-join.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=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1560\\\/a-join-a-day-hash-join-limitations\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1560\\\/a-join-a-day-hash-join-limitations\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"A Join A Day \u2013 Hash Join Limitations\",\"datePublished\":\"2012-12-28T15:00:53+00:00\",\"dateModified\":\"2014-11-13T18:49:49+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1560\\\/a-join-a-day-hash-join-limitations\\\/\"},\"wordCount\":573,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1560\\\/a-join-a-day-hash-join-limitations\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/cross-hash-join.jpg\",\"articleSection\":[\"A Join A Day\",\"General\",\"Series\",\"SQL Server Internals\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1560\\\/a-join-a-day-hash-join-limitations\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1560\\\/a-join-a-day-hash-join-limitations\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1560\\\/a-join-a-day-hash-join-limitations\\\/\",\"name\":\"A Join A Day \u2013 Hash Join Limitations - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1560\\\/a-join-a-day-hash-join-limitations\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1560\\\/a-join-a-day-hash-join-limitations\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/cross-hash-join.jpg\",\"datePublished\":\"2012-12-28T15:00:53+00:00\",\"dateModified\":\"2014-11-13T18:49:49+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"Which logical join types can be conquered by the Hash Join algorithm? Are there any special cases I need to be aware of for which the Hash Join operator might not be the best fit? Read on to get the answers.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1560\\\/a-join-a-day-hash-join-limitations\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1560\\\/a-join-a-day-hash-join-limitations\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1560\\\/a-join-a-day-hash-join-limitations\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/cross-hash-join.jpg\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/cross-hash-join.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1560\\\/a-join-a-day-hash-join-limitations\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"A Join A Day \u2013 Hash Join Limitations\"}]},{\"@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 Hash Join Limitations - sqlity.net","description":"Which logical join types can be conquered by the Hash Join algorithm? Are there any special cases I need to be aware of for which the Hash Join operator might not be the best fit? Read on to get the answers.","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\/1560\/a-join-a-day-hash-join-limitations\/","og_locale":"en_US","og_type":"article","og_title":"A Join A Day \u2013 Hash Join Limitations - sqlity.net","og_description":"Which logical join types can be conquered by the Hash Join algorithm? Are there any special cases I need to be aware of for which the Hash Join operator might not be the best fit? Read on to get the answers.","og_url":"https:\/\/sqlity.net\/en\/1560\/a-join-a-day-hash-join-limitations\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2012-12-28T15:00:53+00:00","article_modified_time":"2014-11-13T18:49:49+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/cross-hash-join.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":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/1560\/a-join-a-day-hash-join-limitations\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/1560\/a-join-a-day-hash-join-limitations\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"A Join A Day \u2013 Hash Join Limitations","datePublished":"2012-12-28T15:00:53+00:00","dateModified":"2014-11-13T18:49:49+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/1560\/a-join-a-day-hash-join-limitations\/"},"wordCount":573,"commentCount":0,"image":{"@id":"https:\/\/sqlity.net\/en\/1560\/a-join-a-day-hash-join-limitations\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/cross-hash-join.jpg","articleSection":["A Join A Day","General","Series","SQL Server Internals"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/1560\/a-join-a-day-hash-join-limitations\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/1560\/a-join-a-day-hash-join-limitations\/","url":"https:\/\/sqlity.net\/en\/1560\/a-join-a-day-hash-join-limitations\/","name":"A Join A Day \u2013 Hash Join Limitations - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/1560\/a-join-a-day-hash-join-limitations\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/1560\/a-join-a-day-hash-join-limitations\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/cross-hash-join.jpg","datePublished":"2012-12-28T15:00:53+00:00","dateModified":"2014-11-13T18:49:49+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"Which logical join types can be conquered by the Hash Join algorithm? Are there any special cases I need to be aware of for which the Hash Join operator might not be the best fit? Read on to get the answers.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/1560\/a-join-a-day-hash-join-limitations\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/1560\/a-join-a-day-hash-join-limitations\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/1560\/a-join-a-day-hash-join-limitations\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/cross-hash-join.jpg","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/cross-hash-join.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/1560\/a-join-a-day-hash-join-limitations\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"A Join A Day \u2013 Hash Join Limitations"}]},{"@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-pa","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1560","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=1560"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1560\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=1560"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=1560"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=1560"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}