SQL中left outer join与inner join 混用时,SQL Server自动优化执行计划
来源:互联网 发布:天天炫斗网络响应超时 编辑:程序博客网 时间:2024/05/19 13:19
以下为示例代码:
set statistics profile offDECLARE @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 onSELECT *FROM @t t left JOIN @tt tt ON t.id1 = tt.id1inner 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.id1inner 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考虑到结果集是一样,所以优化了表的连接顺序和逻辑操作,目的当然是为了提高效率。
- SQL中left outer join与inner join 混用时,SQL Server自动优化执行计划
- SQL中 inner join、 left outer join 、right outer join、 full outer join之间的区别
- SQL join,left join ,right join , inner join , outer join用法解析及HIVE join 优化
- SQL中LEFT JOIN、Inner Join、Right Join、Left Outer Join、full join区别
- join联接查询--SQL中 inner join、 left join 、right join、 outer join之间的区别
- SQL中 inner join、 left join 、right join、 outer join之间的区别[zz]
- SQL中 inner join、 left join 、right join、 outer join之间的区别
- SQL中 inner join、 left join 、right join、 outer join之间的区别
- SQL中 inner join、 left join 、right join、 outer join之间的区别
- SQL中 inner join、 left join 、right join、 outer join之间的区别
- SQL中 inner join、 left join 、right join、 outer join之间的区别
- 了解SQL中 inner join、 left join 、right join、 outer join之间的区别
- sql中left join(left outer join)、inner join的使用,right join(right outer join)的用法和left join的相反就可以了。
- SQL中的各种JOIN(inner join,full outer join,left join,right join,cross join )
- 【SQL】inner join、outer join、cross join和left join、right join、full join的区别
- Sql语句中的inner join ,left outer join ,right outer join ,full join 的理解
- sql left join right join outer join 执行步骤总结
- 关于sql server中的 jion,inner join, left join ,left outer join, right join,right outer join 的几点使用心得
- JSP的四种页面属性范围
- 对于eclipse选择服务器的问题eclipse Cannot create a server using the selected type
- 如何导出IPhone通迅录并清除
- Mysql数据同步给第三方系统的方案探索
- HUB、交换机和路由
- SQL中left outer join与inner join 混用时,SQL Server自动优化执行计划
- xml-rpc简单服务器调用
- 什么是核心层?汇聚层?接入层? 三级网络
- 单播、多播(组播)和广播的区别
- 一个http请求的详细过程
- Struts2中Action配置和使用讲解
- ABAP程序中的处理块
- 关于directshow播放器线程模型注意事项 2
- Android获取系统隐藏服务实现锁屏