IN、EXISTS和表连接三者的效率比较

逃离我推掉我的手 2023-10-07 13:10 34阅读 0赞

一、 IN和EXISTS比较  
在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行查询。此时就会用到IN和EXISTS。  
例如：查询departments表中存在的部门的人数。  
1、 使用IN  
SQL> set timing on  
SQL> select employees.department\_id,count(\*)  
2 from employees  
3 where employees.department\_id in (  
4 select department\_id from departments)  
5 group by department\_id;

DEPARTMENT\_ID COUNT(\*)  
\------------- ----------  
10 1  
20 2  
30 6  
40 1  
50 45  
60 5  
70 1  
80 34  
90 3  
100 6  
110 2

11 rows selected.

Elapsed: 00:00:00.09

2、 使用EXISTS  
SQL> select department\_id,count(\*)  
2 from employees  
3 where exists(  
4 select 1 from departments where departments.department\_id=employees.department\_id)  
5 group by employees.department\_id;

DEPARTMENT\_ID COUNT(\*)  
\------------- ----------  
10 1  
20 2  
30 6  
40 1  
50 45  
60 5  
70 1  
80 34  
90 3  
100 6  
110 2

11 rows selected.

Elapsed: 00:00:00.01

总结：  
IN:确定给定的值是否与子查询或列表中的值相匹配。使用IN时，子查询先产生结果集，然后主查询再去结果集中寻找符合要求的字段列表，符合要求的输出，反之则不输出。  
EXISTS:给定一个子查询，检测行的存在。它不返回列表的值，只返回一个True或False。其运行方式是先运行主查询一次，再去子查询中查找与其对应的结果，如果子查询返回True则输出，反之则不输出。再根据主查询中的每一行去子查询中查询。  
由于IN操作符需要进行确切地比较，而EXISTS只需要验证存不存在，所以使用IN将会比使用EXISTS花费更多的成本，因此能使用EXISTS替代IN的地方，应该尽量使用EXISTS。另外，尽量使用NOT EXISTS替代NOT IN，使用EXISTS替代DISTINCT。

二、表连接和EXISTS比较  
例如：获取部门名为Finance的员工信息  
1、 使用EXISTS  
SQL> select employee\_id,first\_name,salary,department\_id  
2 from employees  
3 where exists(  
4 select 1 from departments where departments.department\_id=employees.department\_id  
5 and department\_name='Finance');

EMPLOYEE\_ID FIRST\_NAME SALARY DEPARTMENT\_ID  
\----------- -------------------- ---------- -------------  
108 Nancy 12008 100  
109 Daniel 9000 100  
110 John 8200 100  
111 Ismael 7700 100  
112 Jose Manuel 7800 100  
113 Luis 6900 100

6 rows selected.

Elapsed: 00:00:00.02

2、 使用表连接  
SQL> select employee\_id,first\_name,salary,employees.department\_id  
2 from employees,departments  
3 where employees.department\_id=departments.department\_id  
4 and department\_name='Finance';

6 rows selected.

Elapsed: 00:00:00.00

从两条SQL语句的执行时间来看，使用连接查询的效率要比使用EXISTS的效率高。当两张表的数据量不大时，使用二者之一就可以，当其中一张表的数据量巨大，或者两张表的数据量都很大的时候，则最好使用连接查询的方式。  
————————————————  
版权声明：本文为CSDN博主「NextAction」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。  
原文链接：https://blog.csdn.net/nextaction/article/details/70851810