Difference between Nested loop join and hash join in SQL Server 2008

Question

What is the primary difference between a nested loop join and a hash join? I dont have any idea about this and all I know is that they appear on the execution plan of a query. Can anyone shed some light on this?

Answer 1

Here are some links. I agree with usr, the question is not very constructive, since to answer it we would just be repeating what has been written elsewhere. If you want to re-phrase your question in a much more specific way, e.g. show us a query and a plan which has one type where you might expect another or want to know why a specific type was chosen, then I think it would be a much more suitable question. The following links should help:

http://blog.tanelpoder.com/2010/10/06/a-the-most-fundamental-difference-between-hash-and-nested-loop-joins/

http://www.madeira.co.il/loop-hash-and-merge-join-types/

http://www.simple-talk.com/sql/performance/execution-plan-basics/

query optimizer operator choice - nested loops vs hash match (or merge)