SQL Server中表联接的执行计划比较

xsw222

　　1、运行如下语句：
　　declare @t table( t1 int)
　　declare @tt table(t1 int ,t2 varchar(10))
　　insert into @t values(1)
　　insert into @t values(2)
　　insert into @t values(3)
　　insert into @t values(4)
　　insert into @tt values(1,'ab')
　　insert into @tt values(1,'acd')
　　insert into @tt values(2,'ab')
　　insert into @tt values(3,'a')
　　insert into @tt values(2,'a')
　　set statistics profile on
　　select *
　　from @t x
　　left outer join @tt y
　　on x.t1 = y.t1
　　where x.t1 = 1 and y.t2 like '%a%'
　　结果：
　　select *  from @t x  left outer join @tt y    on x.t1 = y.t1   where x.t1 = 1 and y.t2 like '%a%'
　　|--Nested Loops(Inner Join)
　　|--Table Scan(OBJECT:(@tt AS [y]), WHERE:(@tt.[t1] as [y].[t1]=(1) AND @tt.[t2] as [y].[t2] like '%a%'))
　　|--Table Scan(OBJECT:(@t AS [x]), WHERE:(@t.[t1] as [x].[t1]=(1)))
　　2、把上面语句中的left outer join 改为inner join后
　　结果：
　　select *  from @t x  inner join @tt y    on x.t1 = y.t1   where x.t1 = 1 and y.t2 like '%a%'
　　|--Nested Loops(Inner Join)
　　|--Table Scan(OBJECT:(@tt AS [y]), WHERE:(@tt.[t1] as [y].[t1]=(1) AND @tt.[t2] as [y].[t2] like '%a%'))
　　|--Table Scan(OBJECT:(@t AS [x]), WHERE:(@t.[t1] as [x].[t1]=(1)))
　　3、如果过滤条件放到 on 子句里面，把上面的SQL改为：
　　select *
　　from @t x
　　left outer join @tt y
　　on x.t1 = y.t1   and    y.t2 like '%a%'
　　where  x.t1 = 1
　　产生的执行计划：
　　select *  from @t x  left outer join @tt y        on x.t1 = y.t1 and y.t2 like '%a%'  where x.t1 = 1
　　|--Nested Loops(Left Outer Join)
　　|--Table Scan(OBJECT:(@t AS [x]), WHERE:(@t.[t1] as [x].[t1]=(1)))
　　|--Table Scan(OBJECT:(@tt AS [y]), WHERE:(@tt.[t1] as [y].[t1]=(1) AND @tt.[t2] as [y].[t2] like '%a%'))
　　其实对比上面的1、2、3，会发现在Table Scan里都是一样的 ：
　　|--Table Scan(OBJECT:(@t AS [x]), WHERE:(@t.[t1] as [x].[t1]=(1)))
　　|--Table Scan(OBJECT:(@tt AS [y]), WHERE:(@tt.[t1] as [y].[t1]=(1) AND @tt.[t2] as [y].[t2] like '%a%'))
　　而且在两个表关联之前，在Table Scan中已经用where子句过滤条件进行过滤，如果用到on子句，就会转化成where子句条件，
　　只是对不同的情况，有不同的处理。
　　用left outer join做关联时：
　　一、当把右边表的字段过滤条件写在where中时（不用转化），在执行计划中是：Nested Loops(inner join)。
　　这里SQL Server 做了优化（inner join的效率优于left outer join）。
　　之所以可以做这样的优化，是由于把右边表的字段过滤条件直接放到where子句中，对联接产生的结果集合中左边表有，而右边表没有的记录，在联接结果集中此字段的值必定为NULL，那么通过这个字段的过滤，必定把NULL的记录都过滤掉了，此时效果等同于inner join，所以把外联接（left outer join）转化成内联接（inner join）。
　　二、当把右边表的字段过滤条件写在on中时（把on转化成where子句），执行计划中是：Nested Loops(left outer join)，无法优化。
　　因为由left outer join产生的结果集与inner join产生的结果集是不同的，无法优化。