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

怼烎@ 2022-04-24 10:52 239阅读 0赞

> ### 一：[浅谈sql中的in与not in,exists与not exists的区别][sql_in_not in_exists_not exists]【还讲了相应的解决方案】 ###
> 
> ### 二：[详解not in与not exists的区别与用法（not in的性能并不差）][not in_not exists_not in] ###
> 
> **【[浅谈sql中的in与not in,exists与not exists的区别以及性能分析][sql_in_not in_exists_not exists 1]，内容简单地含有一与二】**
> 
> ### 实例测试： ###
> 
> **MySQL上：[MYSQL中的NOT IN和NOT EXISTS不一样的时候][MYSQL_NOT IN_NOT EXISTS]**
> 
> **Oracle上：[not exists和not in的对比][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都可操作）** ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDQyNTk1_size_16_color_FFFFFF_t_70][]

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDQyNTk1_size_16_color_FFFFFF_t_70 1][]

![20190423135159711.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDQyNTk1_size_16_color_FFFFFF_t_70 2][]

[sql_in_not in_exists_not exists]: https://www.cnblogs.com/seasons1987/p/3169356.html
[not in_not exists_not in]: https://blog.csdn.net/vixou/article/details/84613672
[sql_in_not in_exists_not exists 1]: https://blog.csdn.net/baidu_37107022/article/details/77278381
[MYSQL_NOT IN_NOT EXISTS]: https://www.jianshu.com/p/ebfec61bcf6a
[not exists_not in]: https://zzwssfd.iteye.com/blog/2045964
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDQyNTk1_size_16_color_FFFFFF_t_70]: /images/20220216/97fe969c3b144238b13d761a23f0eefd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDQyNTk1_size_16_color_FFFFFF_t_70 1]: /images/20220216/f1f0c8c17a2748cab38a50115657fe1f.png
[20190423135159711.png]: /images/20220216/652df8b8298d4275acae6ad453bb0dbe.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDQyNTk1_size_16_color_FFFFFF_t_70 2]: /images/20220216/d64ef7941de3471485eec346fbdeef60.png