Differences between Nested Loop Join and hash join

Nested Loop Join and Hash Join are two types of physical join operators in SQL Server, each optimized for different use cases depending on data size, availability of indexes, and whether the data is sorted. Below are the difference between them.

Sno	Feature	Nested Loop Join	Hash Join
1	Use Case	Small datasets or highly selective joins	Large, unsorted datasets
2	Data Requirements	No specific data order required	No specific data order required
3	Execution Strategy	Iteratively checks each row in one table against rows in the other	Builds a hash table on one input and probes with the other
4	Performance	Efficient for small datasets or when one input is small and indexed	Efficient for large datasets where no indexes exist
5	Index Dependency	May benefit from indexes but does not require them	No index needed
6	Memory Requirement	Low; does not require large memory allocations	High; requires memory to store the hash table
7	Typical Complexity	O(N*M), less efficient as dataset sizes increase	O(N + M), more efficient with large datasets
8	Data Matching Type	Effective for both equality and inequality joins	Best for equality joins (e.g., =)
9	Spill to Disk	Low risk, minimal memory usage	May spill to disk if memory is insufficient
10	Scenarios	Useful for small tables or small subsets of data	Ideal for large, unsorted tables