简介

merge join 对两个表在连接列上按照相同的规则排序，然后再做merge，匹配的输出。

下面这个动态图展示了merge join的详细过程。

 

merge join示例

创建两个表

IF OBJECT_ID('dbo.Tbl10') IS NOT NULL DROP TABLE dbo.Tbl10;CREATE TABLE dbo.Tbl10(  Id INT IDENTITY(1,1),  Val INT,  Fill CHAR(7000) NOT NULL DEFAULT REPLICATE('Fill',1750)); IF OBJECT_ID('dbo.Tbl100') IS NOT NULL DROP TABLE dbo.Tbl100;CREATE TABLE dbo.Tbl100(  Id INT IDENTITY(1,1),  Val INT,  Fill CHAR(7000) NOT NULL DEFAULT REPLICATE('Fill',1750)); INSERT INTO dbo.Tbl10(Val)SELECT TOP(10) 1+ROW_NUMBER()OVER(ORDER BY (SELECT 1))%100FROM sys.all_columns A, sys.all_columns B, sys.all_columns C;  INSERT INTO dbo.Tbl100(Val)SELECT TOP(100) ROW_NUMBER()OVER(ORDER BY (SELECT 1))FROM sys.all_columns A, sys.all_columns B, sys.all_columns C; SELECT index_type_desc, alloc_unit_type_desc, index_depth, page_count, record_countFROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.Tbl10'),NULL,NULL,'SAMPLED'); SELECT index_type_desc, alloc_unit_type_desc, index_depth, page_count, record_countFROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.Tbl100'),NULL,NULL,'SAMPLED');

 执行下面的查询：

SET STATISTICS IO ON;GOSELECT * FROM dbo.Tbl100 AINNER MERGE JOIN dbo.Tbl10 BON A.Val = B.Val;GOSET STATISTICS IO OFF;

 执行计划显示对每个输入都做了排序。

在执行计划的提示上可以看到表被扫描了一次。可以通过SET STATISTICS IO ON，查看：

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Tbl10'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Tbl100'. Scan count 1, logical reads 100, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

两个表的read count说明了每个表都只扫描了一次，我们会发现多出了一个Worktable。

在Sql Server不知道表中join列是唯一的情况下，会准备一个多对多的可能性，因为合并算法的设计是对每一个表中的每一个记录只访问一次，它不能处理多对多的关系。Worktable是在tempdb中的，如果表与表之间的关系是多对多时就会用到Worktable。

merge join操作符有一个多对多的属性，你可以在执行计划工具提示窗口看到。如果此属性设置为“true”，这意味着SQL服务器预计输入有重复的行。

merge join的应用场景：

如果对在表上有索引的或者查询有排序需求的，使用merge join是最好的选择。

merge join的排序十分消耗性能。merge join适合中等数据量的查询，如果是大数据量并且未经过排序的话hash join比较合适。

需要注意的是即使查询的数据只有潜在的多对多的关系时也会导致Worktable的创建和填充，如果有任何重复的值，在Worktable的数据频繁的重读对查询性能也是不利的。