SQL Server has three distinct join methods: Hash, Loop, and Merge. It has three methods so that joining tables is optimized for various conditions, based on data distribution, indexes, volume of data in tables being joined, and the availability the information about those tables.
You can work successfully with SQL Server for years without having to know any of this. In almost all cases, SQL Server will choose the best query plan for joining tables. Unless you review the query plan, you won’t even know what it is doing, or how it is doing it. The joins just work. Sometimes the query doesn’t work well. If you don’t know what it is doing, and can’t understand the query plan, your options to resolve the underlying problem are limited.
So, here is a high level summary of the different kinds of joins. First, a loop join walks through each record from one table, in a loop. For each row, it searches for matching records in the second table. Second, a hash join works when there are no matching indexes. A hash value will be created on the join criteria for each record in both tables. Then the join is performed against the generated hash values. Finally, a merge join is used if there are indexes of all the join columns, in the same order on both tables. The join walks through the index on both tables simultaneously, and joins records when there are matches.
Different things can make one method work better than the other. If you are joining two large tables a merge join is often faster. If you are joining a large table to a small table, a loop table may be the better option. If you are joining tables where one or the other table does not have an index to support the join criteria, a hash join might be what is called for. The neat thing is that the Query Optimizer is doing a fantastic job figuring this out on your behalf, and you’re probably not going to be able to beat it. However, if your query is too slow, now you can look at the query plan, and see what kind of join is being executed. The information there may help you determine if there is something you can do so a better plan may be selected.
This is just a high level introduction to join techniques, with the intention to catch your interest. It is well worth your time to dig into more detailed information if you want to really improve your SQL Optimization Techniques.
Cheers,
Ben