SQL in与exists详解程序员

无可置疑，如果in（）的结果集非常庞大，那么效率必然是低的。

但EXISTS subquery根据其语法可知在SQL中的作用是：检验查询是否返回数据。如果在 Dictionary 对象中指定的关键字存在，返回 True，若不存在，返回 False。exist 相当于存在量词：表示集合存在,也就是集合不为空只作用一个集合.例如 exist P 表示P不空时为真; not exist P表示p为空时 为真 in表示一个标量和一元关系的关系。例如：s in P表示当s与P中的某个值相等时 为真; s not in P 表示s与P中的每一个值都不相等时 为真。

对于in 和 exists的区别: 如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。其实我们区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了 ，另外IN时不对NULL进行处理。
我个人觉得

能用等值连接尽量用等值连接。

n和exists执行时，in是先执行子查询中的查询，然后再执行主查询。而exists查询它是先执行主查询，即外层表的查询，然后再执行子查询。

exists 和 in 在执行时效率单从执行时间来说差不多，exists要稍微优于in。在使用时一般应该是用exists而不用in

如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in,反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。IN时不对NULL进行处理。

        not exists 和 not in 比较时，not exists 的效率比较高。

        为了说明测试结果，我把emp1表中的数据到了315392条。emp2中删除只有2条件数据。测试的依据是执行的时间来说明的。

        emp1中的数据记录情况。

                SQL> select count(*) from emp1;

  COUNT(*)

———-

315392

emp2中的数据记录情况：

SQL> select count(*) from emp2;

 

  COUNT(*)

———-

   2

1、  执行exists查询，要求在emp1中查询出所有存在于emp2的数据总数

 SQL> select count(*) from emp1 where exists ( select null from emp2 where emp1.ename = emp2.ename);

  COUNT(*)

———-

     45056

执行次数十次，最大的一次为0.125S

2、    使用not exists查询出所在不在emp2中的数据总数

SQL> select count(*) from emp1 where not exists ( select null from emp2 where emp1.ename = emp2.ename);

     COUNT(*)

———-

270336

执行次数十次，最大的一次为0.141S

3、执行in 查询，要求在emp1中查询出所有存在于emp2的数据总数

SQL> select count(*) from emp1 where ename in ( select ename from emp2);

  COUNT(*)

———-

     45056

执行十次，最大的一次为0.141S

4、使用not in查询出所在不在emp2中的数据总数

SQL> select count(*) from emp1 where ename not in ( select ename from emp2 );

  COUNT(*)

———-

270336

执行十次，最长一次为0.328S

5、使用in查询，调用外层与子查询的位置，要求查询出存在于emp2中，且存在于emp1中的数据记录数

SQL> select count(*) from emp2 where ename in (select ename from emp1 );

  COUNT(*)

———-

         2

执行次数十次，最长的一次为0.047S

6、使用exists查询，调用外层与子查询的位置，要求查询出存在于emp2中，且存在于emp1中的数据记录数

SQL> select count(*) from emp2 where ename in (select ename from emp1 );

  COUNT(*)

———-

         2

执行次数十次，最长的一次为0.047S

综上所述：在使用in 和 exists时，个人觉得，效率差不多。而在not in 和 not exists比较时，not exists的效率要比not in的效率要高。

当使用in时，子查询where条件不受外层的影响，自动优化会转成exist语句，它的效率和exist一样。(没有验证)

如select * from t1 where f1 in (select f1 from t2 where t2.fx=’x’) 这时，认为in 和 exists效率一样。

IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。