in和exists 区别

in和exists 

in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。

一直以来认为exists比in效率高的说法是不准确的。 

如果查询的两个表大小相当，那么用in和exists差别不大。

如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：

例如：表A（小表），表B（大表）1：select*from Awhere ccin (select ccfrom B) 效率低，用到了A表上cc列的索引；

select*from Awhereexists(select ccfrom Bwhere cc=A.cc) 效率高，用到了B表上cc列的索引。 

相反的2：select*from Bwhere ccin (select ccfrom A) 效率高，用到了B表上cc列的索引；

select*from Bwhereexists(select ccfrom Awhere cc=B.cc) 效率低，用到了A表上cc列的索引。 

notin 和not exists如果查询语句使用了notin 那么内外表都进行全表扫描，没有用到索引；

而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。 

in 与 =的区别select namefrom studentwhere namein ('zhang','wang','li','zhao'); 

与select namefrom studentwhere name='zhang'or name='li'or name='wang'or name='zhao' 的结果是相同的。

说到EXISTS 不得不提到IN 这2个东西其实可以互相转换,但是在某些用法和效率上还是有差别的。
先来说说IN和EXISTS之间的一个区别：
IN不同于EXISTS，当输入列表包含NULL时候,它会产生一个UNKOWN。如IN (a,b,c,null)-->TRUE or UNKOWN.又因为在筛选器中UNKOWN的处理方式是false,
使用IN和使用EXIST产生的结果是一样的，优化器产生的执行计划是一样的。
但是 NOT EXISTS 和 IN之间的逻辑差别就大了,当然前提是输入列表中含有NULL的时候。
create table #test1(a int)
create table #test2(b int,c int)
insert #test1 values(1) 
insert #test1 values(2) 
insert #test1 values(3) 
insert #test2 values(1,3) 
insert #test2 values(null,2)
--NOT IN
select A from #test1 where a not in(select B from #test2) 
/*
A
-----------
(0 行受影响)
*/
--NOT EXISTS
select A from #test1 where NOT EXISTS(SELECT * FROM #test2 WHERE #test1.a=b)
/*
A
-----------
2
3
*/
为什么在NOT IN里面没有返回2和3？ 这是因为 NOT IN (A,B,NULL)返回的结果永远是 NOT TRUE 或者 NOT UNKOWN(UNKOWN),不会返回TRUE。(关于三值逻辑的判断可以参见我以前写的博客)

我再举个例子：
a in (a,b,null) 返回TRUE。 
a not in (a,b,null) 返回not true 即是false 。
c in(a,b,null) c本来不在列表里,但是因为里面有个NULL,SQL 不知道NULL是不是C，所以产生了一个UNKOWN,结果逻辑判断就FALSE.
c not in(a,b,null) 因为里面有个NULL,SQL 不知道NULL是不是C，所以产生了一个 not UNKOWN,即UNKOWN,结果逻辑判断就是FALSE.
这就是为什么NOT IN 和 NOT EXISTS的有时候的逻辑区别了。那么如何解决这个问题呢？你可以在子查询里加个判断,把NULL值去掉
select A from #test1 where a not in(select B from #test2 where b is not null) 
这句的执行计划和NOT EXISTS是一样的。如果不加判断NULL 那么不但结果不对 而且效率也降低不少。因为它加了一个查找B是NULL的步骤，自己可以试试

对了,这里注意一个细节：
select A from #test1 where a not in(select B from #test2) 注意这里的子查询里的SELECT b 有些粗心的朋友会写成select a ,
select A from #test1 where a not in(select A from #test2) 这样不会报错,返回NULL,逻辑上还是可以以的,所以好的习惯可以在子查询的列名前都加上前缀,这样即使写错了也会报错的.
我们再来说说IN 和 EXISTS 效率上的一个区别
in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。
一直以来认为exists比in效率高的说法是不准确的。
如果查询的两个表大小相当，那么用in和exists差别不大。
如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：
例如：表A（小表），表B（大表）
1：
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列的索引。
相反的
2：
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列的索引。
not in 和not exists
如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；
而not extsts 的子查询依然能用到表上的索引。
所以无论那个表大，用not exists都比not in要快。

查询null值时，in会出现短路，exists不会出现短路

另一个不同是，in只能用于一个字段，exists可以用于多个字段

select*from testwhereexists (select*from test);

分析一下exists真的就比in的效率高吗？

    我们先讨论IN和EXISTS。
    select * from t1 where x in ( select y from t2 )
    事实上可以理解为：
    select * 
      from t1, ( select distinct y from t2 ) t2
     where t1.x = t2.y;
    ——如果你有一定的SQL优化经验，从这句很自然的可以想到t2绝对不能是个大表，因为需要对t2进行全表的“唯一排序”，如果t2很大这个排序的性能是不可忍受的。但是t1可以很大，为什么呢？最通俗的理解就是因为t1.x=t2.y可以走索引。但这并不是一个很好的解释。试想，如果t1.x和t2.y都有索引，我们知道索引是种有序的结构，因此t1和t2之间最佳的方案是走merge join。另外，如果t2.y上有索引，对t2的排序性能也有很大提高。
    select * from t1 where exists ( select null from t2 where y = x )
    可以理解为：
    for x in ( select * from t1 )
    loop
       if ( exists ( select null from t2 where y = x.x )
       then 
          OUTPUT THE RECORD!
       end if
    end loop
    ——这个更容易理解，t1永远是个表扫描！因此t1绝对不能是个大表，而t2可以很大，因为y=x.x可以走t2.y的索引。
    综合以上对IN/EXISTS的讨论，我们可以得出一个基本通用的结论：IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。 

我们要根据实际的情况做相应的优化，不能绝对的说谁的效率高谁的效率低，所有的事都是相对的