为啥用not EXISTS 代替 not in？

用EXISTS替代IN、用NOT EXISTS替代NOT IN： 在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。 表展示 　　　　首先，查询中涉及到的两个表，一个user和一个order表，具体表的内容如下： 　　　　user表： 　　　　order表： 　### 　in 　　　　确定给定的值是否与子查询或列表中的值相匹配。in在查询的时候，首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。所以相对内表比较小的时候，in的速度较快。 　　　　具体sql语句如下： ```sql SELECT * FROM `user` WHERE `user`.id IN ( SELECT `order`.user_id FROM `order` ) ``` 这条语句很简单，通过子查询查到的user_id 的数据，去匹配user表中的id然后得到结果。该语句执行结果如下： 　　　　它的执行流程是什么样子的呢？让我们一起来看一下。 　　　　首先，在数据库内部，查询子查询，执行如下代码： ```sql SELECT `order`.user_id FROM `order` ``` 执行完毕后，得到结果如下： 　　　　此时，将查询到的结果和原有的user表做一个笛卡尔积，结果如下： 　　　　此时，再根据我们的user.id IN order.user_id的条件，将结果进行筛选（既比较id列和user_id 列的值是否相等，将不相等的删除）。最后，得到两条符合条件的数据。 ### exists 　　　　指定一个子查询，检测行的存在。遍历循环外表，然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。 　　　　具体sql语句如下： ```sql SELECT `user`.* FROM `user` WHERE EXISTS ( SELECT `order`.user_id FROM `order` WHERE `user`.id = `order`.user_id ) ``` 这条sql语句的执行结果和上面的in的执行结果是一样的。 　　　　但是，不一样的是它们的执行流程完全不一样： 　　　　使用exists关键字进行查询的时候，首先，我们先查询的不是子查询的内容，而是查我们的主查询的表，也就是说，我们先执行的sql语句是： ```sql SELECT `user`.* FROM `user` ``` 得到的结果如下： 　　　　然后，根据表的每一条记录，执行以下语句，依次去判断where后面的条件是否成立： ```sql EXISTS ( SELECT `order`.user_id FROM `order` WHERE `user`.id = `order`.user_id ) ``` 如果成立则返回true不成立则返回false。如果返回的是true的话，则该行结果保留，如果返回的是false的话，则删除该行，最后将得到的结果返回。 ### 区别及应用场景 in 和 exists的区别: 如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键)，如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了 ，另外IN时不对NULL进行处理。 　　　　in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。 ### not in 和not exists 　　　　如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

在子查询中，NOT IN子句将执行一个内部的排序和合并。 无论在哪种情况下，NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。 为了避免使用NOT IN ，我们可以把它改写成外连接(Outer Joins)或NOT EXISTS. 例如： ```sql SELECT … 　　FROM EMP 　　WHERE DEPT_NO NOT IN (SELECT DEPT_NO 　　FROM DEPT 　　WHERE DEPT_CAT=’A’); ``` 为了提高效率。改写为： 　(方法一： 高效) ```sql SELECT …. 　　FROM EMP A,DEPT B 　　WHERE A.DEPT_NO = B.DEPT(+) 　　AND B.DEPT_NO IS NULL 　　AND B.DEPT_CAT(+) = ‘A’ ``` (方法二： 最高效) ```sql SELECT …. 　　FROM EMP E 　　WHERE NOT EXISTS (SELECT ‘X’ 　　FROM DEPT D 　　WHERE D.DEPT_NO = E.DEPT_NO 　　AND DEPT_CAT = ‘A’); ```

exists()后面的子查询被称做相关子查询，他是不返回列表的值的，只是返回一个ture或false的结果。在查询的时候只要有一个记录符合条件，那么它就返回true，这样查询次数少，信息返回量也少。 EXISTS与IN的使用效率的问题，通常情况下采用exists要比in效率高，因为IN不走索引，但要看实际情况具体使用： IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。