How does SQL Server's query optimizer decide which physical join algorithm, such as nested loops, merge join, or hash join, to actually use for a given query?

The optimizer considers the size of the tables involved, whether useful indexes exist on the join columns, and the estimated number of rows on each side, generally preferring nested loops for small result sets with good indexes, merge joins when both inputs are already sorted on the join column, and hash joins for large, unsorted data sets without helpful indexes.

-- View the chosen join algorithm in the execution plan
SET STATISTICS XML ON;
SELECT o.OrderId, c.Name FROM Orders o JOIN Customers c ON o.CustomerId = c.CustomerId;