What is Hash Inner join

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.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manikandan Murugesan9y ago
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.
+1
Ankit Sharma9y ago
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
0