in与exist , not in与not exist 的区别

 

in与exist , not in与not exist 的区别

标签： inserttablemergecnull

2011-08-25 14:45  35789人阅读  评论(5)  收藏  举报

  分类：

SQL（3） 

 

[sql]  view plain  copy

1. in和exists  
2. in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。  
3. 如果查询的两个表大小相当，那么用in和exists差别不大。  
4. 如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：  
5. 例如：表A（小表），表B（大表）1：select * from A where cc in (select cc from B)  
6. 效率低，用到了A表上cc列的索引；select * from A where exists(select cc from B where cc=A.cc)  
7. 效率高，用到了B表上cc列的索引。  
8. 相反的2：select * from B where cc in (select cc from A)  
9. 效率高，用到了B表上cc列的索引；select * from B where exists(select cc from A where cc=B.cc)  
10. 效率低，用到了A表上cc列的索引。  
11.   
12.   
13. not in 和not exists  
14.   
15. 如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。  
16.   
17.    
18.   
19. not in 逻辑上不完全等同于not exists，如果你误用了not in，小心你的程序存在致命的BUG：  
20.   
21. 请看下面的例子：  
22. create table t1 (c1 number,c2 number);  
23. create table t2 (c1 number,c2 number);  
24.   
25. insert into t1 values (1,2);  
26. insert into t1 values (1,3);  
27. insert into t2 values (1,2);  
28. insert into t2 values (1,null);  
29.   
30. select * from t1 where c2 not in (select c2 from t2);  
31. no rows found  
32. select * from t1 where not exists (select 1 from t2 where t1.c2=t2.c2);  
33. c1 c2  
34. 1 3  
35.   
36. 正如所看到的，not in 出现了不期望的结果集，存在逻辑错误。如果看一下上述两个select语句的执行计划，也会不同。后者使用了hash_aj。  
37. 因此，请尽量不要使用not in(它会调用子查询)，而尽量使用not exists(它会调用关联子查询)。如果子查询中返回的任意一条记录含有空值，则查询将不返回任何记录，正如上面例子所示。  
38. 除非子查询字段有非空限制，这时可以使用not in ,并且也可以通过提示让它使用hasg_aj或merge_aj连接  

顶

2

踩

0

 

 

• 上一篇EXISTS与IN的区别
• 下一篇exists和not exists的使用

我的同类文章

SQL（3）

• •DB2 的 case when then else end 条件分支的处理2011-09-30阅读17462
• •EXISTS与IN的区别2011-08-25阅读3079

• •exists和not exists的使用2011-08-27阅读1411

猜你在找

Visual Studio 2015开发C++程序的基本使用

C语言及程序设计提高

C语言及程序设计初步

征服JavaScript高级程序设计与应用实例视频课程

PHP面向对象设计模式

javalangUnsatisfiedLinkErrorA file or directory in the path name does not exist

jbpm54手动配置尤其解决ant installjBPM-eclipseintoeclipsedoes not exist in the project "lBPMinstall"

The name AccordionLayout1 does not exist in the current

ErrorThe name Encoding does not exist in the current context 解决方法

sphinx cmu & devdsp does not exist in ubuntu 1010

查看评论

5楼  Guanglew 2015-12-24 10:19发表 [回复]

来看头像的

4楼  nightseventhunit 2015-02-03 15:26发表 [回复]

哎呦，不错哦。

3楼  wangpo1988 2014-03-18 15:14发表 [回复]

关于not in 和not exists 
select * from t1 where c2 not in (select c2 from t2); 
no rows found 
诚如作者所言，使用上述语句的确返回empty，这个是null的缘故，
我用sql> select * from t1 where t1.c2 not in (select c2 from t2 where t1.c2=t2.c2);语句就可以返回正确的结果；
如果改掉null，使用select * from t1 where c2 not in (select c2 from t2); 也是可以返回正确结果的，但不能保证不存在null，所言加上t1.c2=t2.c2是保险的，
至于效率的话我这就没法知道了，毕竟数据量太少了，作者说的子查询和关联子查询的效率我就不了解了，我也是菜鸟啊。

2楼  asdvdvsdf 2014-02-20 14:26发表 [回复]

好文章，谢谢

1楼  心之所求 2013-12-13 11:11发表 [回复]

长知识了,对我这种菜鸟很有用