sql中exists 和 in的对比

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列的索引。
 
附录
 

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