MS SQL 查询联接运算系列－－合并联接（Merge Join)

get first row R1 from input 1

get first row R2 from input 2

while not at the end of either input

begin

if R1 joins with R2

begin

output (R1, R2)

get next row R2 from input 2

end

else if R1 < R2

get next row R1 from input 1

else

get next row R2 from input 2

end

create table T1 (a int, b int, x char(200))

create table T2 (a int, b int, x char(200))

set nocount on

declare @i int

set @i = 0

while @i < 1000

begin

insert T1 values (@i * 2, @i * 5, @i)

insert T2 values (@i * 3, @i * 7, @i)

set @i = @i + 1

end

select *

from T1 join T2 on T1.a = T2.a

option (merge join)

create unique clustered index T1ab on T1(a, b)

set statistics profile on

select *

from T1 join T2 on T1.a = T2.a

option (merge join)

set statistics profile off

create unique clustered index T2a on T2(a)

set statistics profile on

select *

from T1 join T2 on T1.a = T2.a

set statistics profile off

set statistics profile on

select *

from T1 join T2 on T1.a = T2.a and T1.b = T2.b

set statistics profile off

set statistics profile on

select *

from T1 join T2 on T1.a = T2.a and T1.b > T2.b

set statistics profile off

以下是一个full outer联接的例子：

set statistics profile on

select *

from T1 full outer join T2 on T1.a = T2.a

set statistics profile off

从上面输出的信息发现，现在要处理两个表的所有行（１０００），而不再是仅处理T2的６６８行后就终止联接，由于这里使用的是outer join，它必须读取T2的所有行和与该联接不匹配的那些NULL行．

• 博文量
43
• 访问量
86311