SQL中left outer join与inner join 混用时，SQL Server自动优化执行计划

以下为示例代码： 

set statistics profile off

DECLARE @t TABLE(id1 INT,c VARCHAR(10))

INSERT INTO @t VALUES(1,'ab')
INSERT INTO @t VALUES(2,'abc')
INSERT INTO @t VALUES(3,'abcd')
INSERT INTO @t VALUES(4,'abcde')

DECLARE @tt TABLE(id1 INT,c VARCHAR(10))

INSERT INTO @tt VALUES(9,'s1')
INSERT INTO @tt VALUES(2,'s2')
INSERT INTO @tt VALUES(3,'s3')
INSERT INTO @tt VALUES(5,'s4')

DECLARE @ttt TABLE(id1 INT,c VARCHAR(10))

INSERT INTO @ttt VALUES(2,'r2')
INSERT INTO @ttt VALUES(3,'r3')
INSERT INTO @ttt VALUES(6,'r2')
INSERT INTO @ttt VALUES(8,'r1')

set statistics profile on

SELECT *
FROM @t t 
left JOIN @tt tt
        ON t.id1 = tt.id1
inner JOIN @ttt ttt
        ON t.id1 = ttt.id1

 

产生了如下的执行计划：

SELECT *  FROM @t t   left JOIN @tt tt          ON t.id1 = tt.id1  inner JOIN @ttt ttt          ON t.id1 = ttt.id1
  |--Nested Loops(Left Outer Join, WHERE:(@t.[id1] as [t].[id1]=@tt.[id1] as [tt].[id1]))
       |--Hash Match(Inner Join, HASH:([ttt].[id1])=([t].[id1]), RESIDUAL:(@t.[id1] as [t].[id1]=@ttt.[id1] as [ttt].[id1]))
       |    |--Table Scan(OBJECT:(@ttt AS [ttt]))
       |    |--Table Scan(OBJECT:(@t AS [t]))
       |--Table Scan(OBJECT:(@tt AS [tt])) 

 

这里会先把@t与@ttt进行inner join，这里SQL Server自动优化了连接顺序，先进行inner join可能会产生更小的结果集，然后把结果与@tt 进行 left outer join。

 

把上面最后的sql语句改为：

SELECT *
FROM @t t 
left JOIN @tt tt
        ON t.id1 = tt.id1
inner JOIN @ttt ttt
        ON tt.id1 = ttt.id1

执行计划为：

SELECT *  FROM @t t   left JOIN @tt tt          ON t.id1 = tt.id1  inner JOIN @ttt ttt          ON tt.id1 = ttt.id1
  |--Hash Match(Inner Join, HASH:([ttt].[id1])=([tt].[id1]), RESIDUAL:(@tt.[id1] as [tt].[id1]=@ttt.[id1] as [ttt].[id1]))
       |--Table Scan(OBJECT:(@ttt AS [ttt]))
       |--Hash Match(Inner Join, HASH:([t].[id1])=([tt].[id1]), RESIDUAL:(@t.[id1] as [t].[id1]=@tt.[id1] as [tt].[id1]))
            |--Table Scan(OBJECT:(@t AS [t]))
            |--Table Scan(OBJECT:(@tt AS [tt]))

发现内部，@t与@tt进行inner join，然后@ttt与上述结果进行inner join，操作都成了inner join。

为什么呢？我原来想，应该先是@t与@tt进行left join呀，怎么是inner join，仔细想想整个操作是这样的，@t与@tt进行left join，产生了如下结果集：

@t.id1 @tt.id1

1           null

2           2

3           3

4           null

然后再把这个结果和@ttt进行 inner join，连接条件是 @tt.id1 = @ttt.id1，那么这样最后结果一定会把@tt.id1为null的记录给过滤掉了，所以结果只会出来两条。这样的结果，与一开始@t与@tt就进行inner join是一样的，换句话说结果取决于后面的inner join。

结论：

left outer join和inner join是两种不同的逻辑操作，单纯的操作可能会产生不同的结果集，但是当这两个操作叠加在一起的时候， SQL Server考虑到结果集是一样，所以优化了表的连接顺序和逻辑操作，目的当然是为了提高效率。