What are the main physical join operators in SQL Server?

The three major physical join algorithms are Nested Loops, Hash Match, and Merge Join.

Nested Loops is often effective when one input is small and the other can be accessed efficiently through an index. Hash Match is commonly effective for larger unsorted inputs, especially equality joins, but requires memory and can spill to tempdb if the grant is insufficient. Merge Join can be highly efficient when both inputs are appropriately sorted on the join keys.

No join operator is inherently best. The correct choice depends on row counts, indexes, sorting, memory, and data distribution.