Merge Join operator in SQL Server execution plan

The Merge Join operator is used in an execution plan to combine rows from two sorted datasets. This join method is efficient for joining large tables, provided that both inputs are already sorted on the join key(s). Merge Joins process rows sequentially, allowing SQL Server to avoid re-evaluation of rows once they are matched, making it highly efficient for ordered datasets.

Key Characteristics of Merge Join

Ø  Sorted Inputs: The Merge Join works best when both inputs are pre-sorted on the join key(s).

Ø  Efficient for Large Tables: Particularly efficient for joining large, ordered tables.

Ø  Performance Boost with Indexes: Merge Joins are optimal when both tables are indexed on the join key, as this provides pre-sorted data without additional sorting.

Icon of Merge join

See example

SELECT OH.orderdate,        OD.orderqty,        OD.productid,        OD.unitprice FROM   sales.salesorderheader AS OH        JOIN sales.salesorderdetail AS OD          ON OH.salesorderid = OD.salesorderid

Running this script and see the execution plan

 

Merge Join (left outer join) operator

If we are using left join in place of join it will generate merge join (Left Outer Join), see below query.

SELECT OH.orderdate,        OD.orderqty,        OD.productid,        OD.unitprice FROM   sales.salesorderheader AS OH        left JOIN sales.salesorderdetail AS OD          ON OH.salesorderid = OD.salesorderid

See the execution plan