以下为示例代码:
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考虑到结果集是一样,所以优化了表的连接顺序和逻辑操作,目的当然是为了提高效率。