软件开发培训班 >> 编程开发 >> SQL


对比SQL中简单嵌套查询与非嵌套查询的异同

　　本文标签：SQL

　　本文将讨论的是SQL中简单嵌套查询与非嵌套查询的异同，通过这些来更加深刻理解SQL语句  。

　　某天的工作是修复某个项目的bug，接着就发现，其sql极其混乱，有非常多的left join和in操作，还有嵌套查询（只有一个表的嵌套查询）  。不知道看到过哪里的资料说，嵌套查询速度慢，于是我把全部嵌套查询都改成join的形式，嵌套查询里面的where条件，我都写到join...on后面去了  。突然一个想法冒出来：筛选条件跟在join...on后面 和 跟在整个sql语句最后面的where后面有什么区别呢？还有嵌套查询真的慢么？于是便有下面的测试产生，数据库环境为MS SQL 2005  。

　　一，inner join

　　先看看非嵌套查询： 

  1. a.select * from t1  
  2. inner join t2 on t1.id = t2.id  
  3. inner join t3 on t1.id = t3.id  
  4. where t1.a=1 and t2.b=1 and t3.c=1  
  5.  
  6. b.select * from t1  
  7. inner join t2 on t1.id = t2.id and t2.b=1   
  8. inner join t3 on t1.id = t3.id  
  9. where t1.a=1 and t3.c=1  
  10.  
  11. c.select * from t1  
  12. inner join t2 on t1.id = t2.id and t2.b=1   
  13. inner join t3 on t1.id = t3.id and t3.c=1  
  14. where t1.a=1 

　　在上面三个非嵌套查询，让“and t2.b=1”和“and t3.c=1”分别在join...on和where之间游走，用Management Studio选中“包含实际的执行计划”并执行这三条语句，都得出下面这个执行计划  。

　　执行计划

　　三个“聚集索引扫描”的谓词从上到下分别是：

　　1.t3.c=1

　　2.t1.a=1 (seek谓词：t1.id=t3.id)

　　3.t2.b=1 (seek谓词：t2.id=t3.id)

　　故可以认为：在MS SQL2005中，条件跟在join...on后面 和 跟在where后面是等价的  。

　　接着看嵌套查询：

  1. d.select * from t1  
  2. inner join (select * from t2 where t2.b=1)a on t1.id=a.id  
  3. inner join t3 on t1.id = t3.id  
  4. where t1.a=1 and t3.c=1  
  5.  
  6. e.select * from t1  
  7. inner join (select * from t2 where t2.b=1)a on t1.id=a.id  
  8. inner join (select * from t3 where t3.c=1)b on t1.id=b.id  
  9. where t1.a=1  
  10.  
  11. f.elect * from t1  
  12. inner join (select t3.id,t2.b,t3.c from t3 inner join t2 on t2.id = t3.id where t2.b=1 and t3.c=1)a on t1.id=a.id  
  13. where t1.a=1 

　　第一句sql语句把t2的查询变成子查询，第二句sql语句把t2,t3分别变成子查询，第三句把t2和t3的查询合成一个子查询，再看看实际的执行计划：

　　执行计划

　　跟上面非嵌套查询的执行计划一模一样  。

　　故可以认为：简单（注意是简单的，复杂的情况得另外考虑）嵌套查询和其相对应的非嵌套查询形式，执行效率是一样的（网上一些文章指出这是MS SQL优化器针对这些嵌套查询进行了优化）  。

　　

　　接着，在上面两个执行计划的图中又发现一个小问题，为什么明明是select t1 inner join t2 inner join t3，执行计划却把t1和t3先inner join(t1.id = t3.id)再跟t2 inner join（t2.id = t3.id）起来？

　　经过三个表，四个表，五个表进行连接测试，发现这些顺序都是不确定的  。很可能这些顺序是根据SQL优化器内的算法所决定的，由于没有源代码，所以无从考究  。

　　(感谢Keep Walking的补充:

“可以指定顺序，force order选项，和keep plan选项

　　

数量级，索引，统计的不同都可以导致顺序变化”  。
我Google了一下option force order和option keep plan，发现SQL优化器做了很多事情，在这文章就不列出来了，大家有兴趣可以Goo一下  。)

　　

　　PS：

　　1.经测试，在join on后面t1.id = t2.id与t2.id = t1.id等价

　　如果发现这文章有错误，欢迎指出  。

　　原文标题：SQL语句分析：ON与WHERE的比较_简单嵌套查询与非嵌套查询的比较

　　链接：http://www.cnblogs.com/StephenHuang/archive/2010/01/03/1637846.html