Nested Loops vs Hash Join vs Merge Join
These are physical operators SQL Server uses to join two datasets.
| Join operator | How it works | Best suited for | Main concern |
|---|---|---|---|
| Nested Loops | Takes each row from the first input and searches for matching rows in the second input | Small outer result with an index on the inner table | Slow when many rows are processed |
| Hash Join | Creates a hash table from the smaller input and matches rows from the other input | Large, unsorted datasets without useful indexes | Uses more memory and may spill to tempdb |
| Merge Join | Reads two sorted inputs together and matches them | Large datasets already sorted on the join columns | Sorting can be expensive if inputs are not already sorted |
Simple example
SELECT *
FROM Orders o
JOIN Customers c
ON o.CustomerId = c.CustomerId;SQL Server may choose:
-
Nested Loops: Only a few orders are selected.
-
Hash Join: Many unsorted orders and customers are joined.
-
Merge Join: Both inputs are already ordered by
CustomerId.
Key points
-
SQL Server normally selects the join operator automatically.
-
Indexes, data size, sorting and statistics affect its decision.
-
No join type is always best.
-
Incorrect row estimates can cause SQL Server to choose an inefficient join.