灭霸头条

sql中exists 和 in的对比

2023-02-20 15:34 由 蚌壳里夜有多长 发表于 #数据库

EXISTS语法解析

EXISTS 运算符用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False。

比如说下边的语法，子查询中的column_name可以和主查询中的列名字不一样，因为子查询只返回yes or no。

subquery 是一个受限的 SELECT 语句 (不允许有 COMPUTE 子句和 INTO 关键字)。

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

IN 语法解析

IN 操作符允许您在 WHERE 子句中规定多个值

SELECT column1, column2, ...
FROM table_name
WHERE column IN (value1, value2, ...);

IN比较好理解 就是判断主查询体中的字段的值是否在子查询结果集中存在

 

分析IN和EXISTS查询过程

从效率来看： 

1) select * from T1 where exists(select 1 from T2 where T1.a=T2.a) ;

    T1数据量小而T2数据量非常大时，T1<<T2 时，1) 的查询效率高。

2) select * from T1 where T1.a in (select T2.a from T2) ;

     T1数据量非常大而T2数据量小时，T1>>T2 时，2) 的查询效率高。
3) select * from T1 where T1.a in (select T2.a from T2) ;

     T1数据量非常大而T2数据量也非常大时，3) 的查询效率高。
4) select * from T1 where T1.a in（exists） (select T2.a from T2) ;

     T1数据量非常小而T2数据量也非常小时，4) 的查询效率都无所谓了差别不大。
 
简而言之，一般式：外表大，用IN；内表大，用EXISTS。

 执行方式： 

通过使用EXISTS，Oracle会首先检查主查询，然后运行子查询直到它找到第一个匹配项，这就节省了时间。Oracle在执行IN子查询时，首先执行子查询，并将获得的结果列表存放在一个加了索引的临时表中。在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以后再执行主查询。这也就是使用EXISTS比使用IN通常查询速度快的原因。

 in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。

  一直以来认为exists比in效率高的说法是不准确的。

 
如果查询的两个表大小相当，那么用in和exists差别不大。

如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：

例如：表A（小表），表B（大表）
1：
select * from A where cc in (select cc from B)
效率低，用到了A表上cc列的索引；
select * from A where exists(select cc from B where cc=A.cc)
效率高，用到了B表上cc列的索引。
 
相反的
2：
select * from B where cc in (select cc from A)
效率高，用到了B表上cc列的索引；
select * from B where exists(select cc from A where cc=B.cc)
效率低，用到了A表上cc列的索引。
 
附录
 

热门相关：地球第一剑   情生意动   网游之逆天飞扬   霸皇纪   明月照大江