| Base of Difference | Merge Join | Hash Join | Nested loop Join |
| Index | We use Merge join for tables that have an index on join column and this index is either clustered or cover non-cluster index. | We use Hash Join for tables with no index (or) either of the big tables has indexed. | We use Nested Loop Join for small table with index (or) either of the big tables have indexed. |
| Functioning | As this need an index for both the table so it is already sorted and easily match and return the data. | As Hash table worked good with big tables with no index and run the query parallel and this will give the best performance. | As we got that it is good for small tables like we compares each row from one table to each row from the other table "looping". |
| Advantage | Merge join are faster and use less memory than hash join.
| Hash join parallelize and scale better than any of join and it is good at maximizing throughput in data warehouses. | Nested loop retrieve first few rows very earlier without having to wait for entire result set. |