Interview Question of the Week #041 – What is a Merge Join?

“What is a Merge Join?” is a good interview question because it separates the SQL result you request from the physical method the optimizer chooses to produce it.

Question: What does SQL Server’s Merge Join operator do?

Answer: It walks two inputs ordered by the join keys, comparing the current values and advancing through the streams to find matches. If suitable index order is already available, this can be efficient. If the engine must sort large inputs first, those Sort operators add cost.

My old article mixed wording about an Integration Services Merge Join transformation into an explanation of the database engine plan operator. They are separate features. In a SQL Server query plan, inspect the actual input order, join keys, and any Sort operations rather than assuming the columns are “physically sorted” because a primary key exists.

Here is a small standalone query you can use to inspect the engine operator. The hint is for demonstration only; do not treat it as a default production tuning rule.

CREATE TABLE #MergeLeft
(
    ItemId int NOT NULL PRIMARY KEY CLUSTERED,
    LeftValue varchar(20) NOT NULL
);
CREATE TABLE #MergeRight
(
    ItemId int NOT NULL PRIMARY KEY CLUSTERED,
    RightValue varchar(20) NOT NULL
);
INSERT #MergeLeft VALUES (1, 'One'), (2, 'Two'), (4, 'Four');
INSERT #MergeRight VALUES (1, 'First'), (3, 'Third'), (4, 'Fourth');

SELECT l.ItemId, l.LeftValue, r.RightValue
FROM #MergeLeft AS l
JOIN #MergeRight AS r ON r.ItemId = l.ItemId
OPTION (MERGE JOIN);

Actual Merge Join: 2 rows. Captured in SSMS on SQL Server 2025.

Enable the actual execution plan and inspect whether both clustered index inputs provide the order, or whether Sort appears. The matching IDs here are 1 and 4. A many-to-many Merge Join needs more work when both sides have duplicate key values; SQL Server can use a worktable to process those matches. For a real workload, compare the complete cost with Nested Loops and Hash Join before deciding that Merge is fastest.

The original pubs example

My original query used the historical pubs sample. If that archived schema is installed, it illustrates the same join hint. The current runnable temporary-table example above avoids requiring that old database:

USE pubs;
GO
SELECT a.au_id
FROM dbo.authors AS a
JOIN dbo.titleauthor AS b ON b.au_id = a.au_id
OPTION (MERGE JOIN);

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.