How does a Hash Match join work?

A Hash Match join generally builds a hash table from one input and probes it using rows from the other input. It is commonly used for equality joins involving relatively large datasets when useful ordering or indexes are unavailable.

Hash joins require memory. If the memory grant is insufficient, SQL Server may spill hash data to tempdb, increasing I/O and elapsed time.

A Hash Match is not automatically evidence of a bad query. It can be the most efficient operator for the workload.