The total cost for merge joins depends on:
The type of merge join.
Full-merge joins do not require sorts and worktables.
For right-merge and left-merge joins, one side of the join is selected into a worktable, then sorted.
For sort-merge joins, both sides of the join are selected into worktables, and each worktable is sorted.
The type of index used to scan the tables while performing the merge step.
The locking scheme of the underlying table: costing models for most scans are different for allpages locking than data-only locking. Clustered index access cost on data-only-locked tables is more comparable to nonclustered access.
Whether the query is executed in serial or parallel mode.
Whether the outer table has duplicate values for the join key.
In general, when comparing costs between a nested-loop join and a merge join for the same tables, using the same indexes, the cost for the outer table remains the same. Access to the inner table costs less for a merge join because the scan remains positioned on the leaf pages as matching values are returned, saving the logical I/O cost of scanning down the index from the root page each time.
