浅谈sql中的in与not in,exists与not exists的区别

转载 2015年07月10日 16:46:04

1、in和exists

in是把外表和内表作hash连接,而exists是对外表作loop循环,每次loop循环再对内表进行查询,一直以来认为exists比in效率高的说法是不准确的。如果查询的两个表大小相当,那么用in和exists差别不大;如果两个表中一个较小一个较大,则子查询表大的用exists,子查询表小的用in;

例如:表A(小表),表B(大表)

select * from A where cc in(select cc from B)  -->效率低,用到了A表上cc列的索引;

select * from A where exists(select cc from B where cc=A.cc)  -->效率高,用到了B表上cc列的索引。

相反的:

select * from B where cc in(select cc from A)  -->效率高,用到了B表上cc列的索引

select * from B where exists(select cc from A where cc=B.cc)  -->效率低,用到了A表上cc列的索引。

 

2、not in 和not exists

not in 逻辑上不完全等同于not exists,如果你误用了not in,小心你的程序存在致命的BUG,请看下面的例子:

复制代码
create table #t1(c1 int,c2 int);

create table #t2(c1 int,c2 int);

insert into #t1 values(1,2);

insert into #t1 values(1,3);

insert into #t2 values(1,2);

insert into #t2 values(1,null);

 

select * from #t1 where c2 not in(select c2 from #t2);  -->执行结果:无

select * from #t1 where not exists(select 1 from #t2 where #t2.c2=#t1.c2)  -->执行结果:1  3
复制代码

正如所看到的,not in出现了不期望的结果集,存在逻辑错误。如果看一下上述两个select 语句的执行计划,也会不同,后者使用了hash_aj,所以,请尽量不要使用not in(它会调用子查询),而尽量使用not exists(它会调用关联子查询)。如果子查询中返回的任意一条记录含有空值,则查询将不返回任何记录。如果子查询字段有非空限制,这时可以使用not in,并且可以通过提示让它用hasg_aj或merge_aj连接。

如果查询语句使用了not in,那么对内外表都进行全表扫描,没有用到索引;而not exists的子查询依然能用到表上的索引。所以无论哪个表大,用not exists都比not in 要快。

 

3、in 与 = 的区别

select name from student where name in('zhang','wang','zhao');

select name from student where name='zhang' or name='wang' or name='zhao'

的结果是相同的。

浅谈sql中的in与not in,exists与not exists的区别以及性能分析

浅谈sql中的in与not in,exists与not exists的区别
  • baidu_37107022
  • baidu_37107022
  • 2017年08月16日 21:25
  • 973

SqlServer中in和exists的区别效率问题

in 和exists in是把外表和内表作hash 连接,而exists 是对外表作loop 循环,每次loop 循环再对内表进行查询。 一直以来认为exists 比in 效率高的说法是不准确...
  • chenghaibing2008
  • chenghaibing2008
  • 2015年08月07日 15:39
  • 2877

数据库中in和exists关键字的区别

数据库中in和exists关键字的区别       in 是把外表和内表作hash join,而exists是对外表作loop,每次loop再对内表进行查询。      一直以来认为exist...
  • zhanglu0223
  • zhanglu0223
  • 2013年10月18日 17:54
  • 7423

in、or、exists区别

in 和or区别: 如果in和or所在列有索引或者主键的话,or和in没啥差别,执行计划和执行时间都几乎一样。 如果in和or所在列没有 索引的话,性能差别就很大了。在没有索引的情况下,随着in或...
  • qq_34783818
  • qq_34783818
  • 2017年03月07日 20:04
  • 323

sql中exists的用法及与in的比较

EXISTS用于检查子查询是否至少会返回一行数据,该子查询实际上并不返回任何数据,而是返回值True或False 有一个查询如下: 复制代码 代码如下: SELECT c.Cu...
  • zisongjia
  • zisongjia
  • 2016年09月19日 11:52
  • 446

not in 与not exists区别

1、对于not  exists查询,内表存在空值对查询结果没有影响;对于not  in查询,内表存在空值将导致最终的查询结果为空。 2、对于not  exists查询,外表存在空值,存在空值的那条记...
  • u012191627
  • u012191627
  • 2015年04月16日 16:47
  • 688

sql语句中exists和in用法的区分

初学java两个多月,这两天在整理老师的笔记对数据库中的exists和in的用法分不清楚,从网上搜了些资料,作了点总结。 一、问题起因 起初是由两条语句执行结果不同,语句如下: --分析以下结果...
  • U___U
  • U___U
  • 2015年03月27日 15:25
  • 4510

浅谈sql中的in与not in,exists与not exists的区别

 浅谈sql中的in与not in,exists与not exists的区别   1、in和exists in是把外表和内表作hash连接,而exists是对外表作loop循环,每次l...
  • yuanyuanispeak
  • yuanyuanispeak
  • 2014年11月03日 15:45
  • 278

Sql中EXISTS与IN的效率问题

根据两张表大小不同选择EXSIST、IN
  • u014134766
  • u014134766
  • 2016年05月18日 12:29
  • 3227

exists与inner join的效率问题

    二者并没有严格的效率高低之分,甚至依赖于数据库中数据的组织方式。    exists的效率依赖于匹配度,join的效率则比较稳定。比如,对 select * from tableA as ta...
  • barfoo
  • barfoo
  • 2009年05月27日 16:08
  • 2749
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:浅谈sql中的in与not in,exists与not exists的区别
举报原因:
原因补充:

(最多只允许输入30个字)