How does a Merge Join work?

A Merge Join processes two inputs ordered by the join keys and advances through them to match values. It is particularly effective for large, already-sorted datasets.

If the inputs are not naturally ordered by suitable indexes, SQL Server may need Sort operators before the merge, which can change the cost significantly.

Merge joins commonly support equality joins and can be very efficient when appropriate ordering already exists.