面试题练习（14）——MySQL与Oracle的“in与exists、not in与not exists”【主要是讲解Oracle的】

 

一：浅谈sql中的in与not in,exists与not exists的区别【还讲了相应的解决方案】

二：详解not in与not exists的区别与用法（not in的性能并不差）

【浅谈sql中的in与not in,exists与not exists的区别以及性能分析，内容简单地含有一与二】

实例测试：

MySQL上：MYSQL中的NOT IN和NOT EXISTS不一样的时候

Oracle上：not exists和not in的对比

总结：

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

2、not in 逻辑上不完全等同于not exists，如果你误用了not in，小心你的程序存在致命的BUG。

3、 如果查询语句使用了not in，那么对内、外表都进行全表扫描，没有用到索引；而not exists的子查询依然能用到表上的索引。所以无论哪个表大，用not exists都比not in 要快。【select * from B where cc not in(select cc from A)、select * from B where not exists ( select cc from A where A.y = B.x ) 】

4、not in语句比not exists语句效率差很多：not exists语句很显然就是一个简单的两表关联，内表与外表中存在空值本身就不参与关联，在CBO(Cost Based Optimizer，基于成本的优化器)中常用的执行计划是hash join；而not in语句的关联谓词是filter，它类似于两表关联中的nested loop，也就是跑两层循环【not in不能使用hash join作为执行计划，因为：内表或外表中存在空值对最终结果产生的影响是hash join无法实现的，并且hash join不支持空值放入hash桶中，所以它没办法处理外表和内表中存在的空值，效率与正确性放在一起时，肯定是要选择正确性，所以oracle必须放弃效率，保证正确性，采用filter谓词】

5、NOT EXISTS 与 NOT IN 不能完全互相替换，看具体的需求。如果选择的列可以为空，则不能被替换。 

6、对于not exists查询，内表存在空值对查询结果没有影响；对于not in查询，内表存在空值将导致最终的查询结果为空。

7、对于not exists查询，外表存在空值，存在空值的那条记录最终会输出；对于not in查询，外表存在空值，存在空值的那条记录最终将被过滤，其他数据不受影响。

8、对于not in 和 not exists的性能区别： （可以与“1、”作比较）
not in 只有当子查询中，select 关键字后的字段有not null约束或者有这种暗示时用not in,另外如果主查询中表大，子查询中的表小但是记录多，则应当使用not in,并使用anti hash join. 
如果主查询表中记录少，子查询表中记录多，并有索引，可以使用not exists,另外not in最好也可以用/*+ HASH_AJ */或者外连接+is null 【下面有个例子】
NOT IN 在基于成本的应用中较好 

面试题目：用一条SQL语句 查询出表STU每门课都大于80分的学生姓名：（在MySQL或SQLServer都可操作）

MySQL中：“in与not in”、“ exists与 not exists”比较：