What is Hash Inner join

Question

Hey guys Can any one explain what is Hash inner join and how it differs from regular inner join?

Manikandan Murugesan · Answer

Just to comment on what a HASH join is. There are basically three types of join - HASH MERGE and LOOP. Hash is whereby a hash code is used as an O(n) lookup into the other rowset. This one is good for large unordered data sets. Merge is whereby rows are 'consumed' from each side of the join in the correct sequence - meaning that each side must be sequenced. This one is good for any size of ordered data set. Loop is whereby each row is matched against every other row from the other side of the join. Imagine knowing the title of a book then comparing the title with every book in the library. While this sounds hurrendous it will actually be more efficient for small unordered data sets than a hash join. The query optimizer assigns these roles so that the smaller of the two inputs is the build input. Hash joins are used for many types of set-matching operations: inner join ; left right and full outer join ; left and right semi- join ; intersection; union; and difference.

Ankit Sharma · Answer

Hi Ravi Hash Join is generally used by SQL Server Optimizer when the data is huge and there are no usable index defined on the table. Please refer to below URLs to get more understanding https://technet.microsoft.com/en-us/library/ms189313(v=sql.105).aspx http://www.madeiradata.com/loop-hash-and-merge-join-types/ http://sqlity.net/en/1490/a-join-a-day-the-hash-join/ Hope this helps