Oracle中关于in和exists，not in 和 not exists in和exists in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。 not exists:做NL，对子查询先查，有个虚表，有确定值，所以就算子查询有NULL最终也有值返回 not in:做hash，对子查询表建立内存数组，用外表匹配，那子查询要是有NULL那外表没的匹配最终无值返回。 一直以来认为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列的索引。 not in 和not exists 如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引； 而not extsts 的子查询依然能用到表上的索引。 所以无论那个表大，用not exists都比not in要快。 一直听到的都是说尽量用exists不要用in，因为exists只判断存在而in需要对比值，所以exists比较快，但看了看网上的一些东西才发现根本不是这么回事。 下面这段是抄的 Select * from T1 where x in ( select y from T2 ) 执行的过程相当于: select * from t1, ( select distinct y from t2 ) t2 where t1.x = t2.y; select * from t1 where exists ( select null from t2 where y = x ) 执行的过程相当于: for x in ( select * from t1 ) loop if ( exists ( select null from t2 where y = x.x ) then OUTPUT THE RECORD end if end loop 从我的角度来说，in的方式比较直观，exists则有些绕，而且in可以用于各种子查询，而exists好像只用于关联子查询（其他子查询当然也可以用，可惜没意义）。 由于exists是用loop的方式，所以，循环的次数对于exists影响最大，所以，外表要记录数少，内表就无所谓了，而in用的是hash join，所以内表如果小，整个查询的范围都会很小，如果内表很大，外表如果也很大就很慢了，这时候exists才真正的会快过in的方式。 not in 和not exists 如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引； 而not extsts 的子查询依然能用到表上的索引。 所以无论那个表大，用not exists都比not in要快。 也就是说，in和exists需要具体情况具体分析，not in和not exists就不用分析了，尽量用not exists就好了。 典型的连接类型共有3种： 排序 - - 合并连接(Sort Merge Join (SMJ) ) 嵌套循环(Nested Loops (NL) ) 哈希连接(Hash Join) 嵌套循环和哈希连接的算法还是有不同，在理论上哈希连接要快过排序和nl，当然实际情况比理论上有复杂的多，不过两者还是有差异的. 1 关联子查询与非关联子查询 关联子查询需要在内部引用外部表，而非关联子查询不要引用外部表。对于父查询中处理的记录来说，一个关联子查询是每行计算一次，然而一个非关联子查询只会执行一次，而且结果集被保存在内存中（如果结果集比较小），或者放在一张oracle临时数据段中（如果结果集比较大）。一个“标量”子查询是一个非关联子查询，返回唯一记录。如果子查询仅仅返回一个记录，那么oracle优化器会将结果缩减为一个常量，而且这个子查询只会执行一次。 /*select * from emp where deptno in (select deptno from dept where dept_name='admin');*/ 2.如何选择？ 根据外部查询，以及子查询本身所返回的记录的数目。如果两种查询返回的结果是相同的，哪一个效率更好？ 关联子查询的系统开销：对于返回到外层查询的记录来说，子查询会每次执行一次。因此，必须保证任何可能的时候子查询都要使用索引。 非关联子查询的系统开销：子查询只会执行一次，而且结果集通常是排好序的，并保存在临时数据段中，其中每一个记录在返回时都会被父级查询引用，在子查询返回大量记录的情况下，将这些结果集排序回增大系统的开销。 所以：如果父查询只返回较少的记录，那么再次执行子查询的开销不会非常大，如果返回很多数据行，那么直查询就会执行很多次。 如果子查询返回较少的记录，那么为内存中保存父查询的结果集的系统开销不会非常大，如果子查询返回多行，那么需要将结果放在临时段上，然后对数据段排序，以便为负查询中的每个记录服务。 3结论：1）在使用一个关联子查询是，使用in 或者 exists子句的子查询执行计划通常都相同 2）exists子句通常不适于子查询 3）在外部查询返回相对较少记录时，关联子查询比非关联子查询执行得要更快。 4）如果子查询中只有少量的记录，则非关联子查询会比关联子查询执行得更快。 4 子查询转化：子查询可以转化为标准连接操作 1）使用in的非关联子查询（子查询唯一） 条件：1）在整个层次结构中最底层数据表上定义唯一主键的数据列存在于子查询的select列表中 2）至少有个定义了唯一主键的数据列在select列表中，而且定义唯一主键的其他数据列都必须有指定的相等标准，不管是直接指定，还是间接指定。 2）使用exists子句的关联子查询 条件：对于相关条件来说，该子查询只能返回一个记录。 5。not in和not exists调整 1）not in 非关联子查询：转化为in写法下的minus子句 2）not exists关联子查询：这种类型的反连接操作会为外部查询中每一个记录进行内部查询，除了不满足子查询中where条件的内部数据表以外，他会过滤掉所有记录。 可以重写：在一个等值连接中指定外部链接条件，然后添加select distinct eg:select distinct ... from a,b where a.col1 = b.col1(+) and b.col1 is null 6。在子查询中使用all any 1. 1. 简介 本文简要介绍了关联子查询、非关联子查询、IN & EXISTS 、 NOT IN & NOT EXISTS之间的区别；同时对不同数据库版本下CBO对IN & EXISTS & NOT IN & NOT EXISTS的处理做了一定的阐述。 2. os、数据库版本以及测试数据 os：windows 2000 server sp4 db：oracle 10.1.0.2 set time on set timing on drop table outer_large_t / create table outer_large_t (id number, c1 varchar2(100), c2 varchar2(100) ) / create index idx_outer_large_t on outer_large_t(id) / drop table outer_small_t / create table outer_small_t as select *from outer_large_t where 1=2 / create index idx_outer_small_t_id on outer_small_t(id) / drop table inner_large_t / create table inner_large_t (id number, c3 varchar2(100), c4 varchar2(100) ) / create index idx_inner_large_t_1 on inner_large_t(id,c3) / drop table inner_small_t / create table inner_small_t (id number, c3 varchar2(100), c4 varchar2(100) ) / create index idx_inner_small_t on inner_small_t(id,c3) / 3. 2.关联子查询和非关联子查询 测试数据: truncate table outer_large_t / truncate table inner_large_t / declare begin for i in 1..50000 loop insert into outer_large_t values (i,'test','test'); end loop; for i in 30000..100000 loop insert into inner_large_t values (i,'test','test'); end loop; commit; end; / analyze table outer_large_t compute statistics for table for all indexes / analyze table inner_large_t compute statistics for table for all indexes / 非关联子查询形如： select count(*) from outer_large_t where id not in (select id from inner_large_t) / 子查询与父查询没有关联。 关联子查询形如： select count(*) from outer_large_t outer_t where not exists (select id from inner_large_t where id = outer_t.id) / 子查询与父查询存在关联id = outer_t.id。 非关联子查询对于exists和not exists是没有意义的。 看如下实验： 11:17:00 test@GZSERVER> select count(*) from outer_large_t 11:17:02 2 where id not in 11:17:02 3 (select id from inner_large_t) 11:17:02 4 / COUNT(*) ---------- 29999 已用时间: 00: 00: 00.04 11:17:02 test@GZSERVER> select count(*) from outer_large_t 11:17:02 2 where id in 11:17:02 3 (select id from inner_large_t) 11:17:02 4 / COUNT(*) ---------- 20001 已用时间: 00: 00: 00.01 11:17:02 test@GZSERVER> select count(*) from outer_large_t outer_t 11:17:02 2 where not exists 11:17:02 3 (select id from inner_large_t) 11:17:02 4 / COUNT(*) ---------- 0 已用时间: 00: 00: 00.00 11:17:02 test@GZSERVER> select count(*) from outer_large_t outer_t 11:17:02 2 where exists 11:17:02 3 (select id from inner_large_t) 11:17:02 4 / COUNT(*) ---------- 50000 已用时间: 00: 00: 00.00 11:17:03 test@GZSERVER> 非关联子查询使用not exists的话父查询总是返回0，使用exists总是返回父查询的查询结果集。

1、关于在 Oracle8i 时代中in和exists的区别 这里有条SQL语句：select * from A where id in(select id from B) 以上查询使用了in语句,in()只执行一次,它查出B表中的所有id字段并缓存起来.之后,检查A表的id是否与B表中的id相等,如果相等则将A表的记录加入结果集中,直到遍历完A表的所有记录; 它的查询过程类似于以下过程 List resultSet=[]; Array A=(select * from A); Array B=(select id from B); for(int i=0;i<A.length;i++) { for(int j=0;j<B.length;j++) { if(A[i].id==B[j].id) { resultSet.add(A[i]); break; } } } return resultSet; 可以看出,当B表数据较大时不适合使用in(),因为它会B表数据全部遍历一次. 如:A表有10000条记录,B表有1000000条记录,那么最多有可能遍历10000*1000000次,效率很差. 再如:A表有10000条记录,B表有100条记录,那么最多有可能遍历10000*100次,遍历次数大大减少,效率大大提升. 结论1:in()适合B表比A表数据小的情况 这里还有一条SQL语句： select a.* from A a where exists(select 1 from B b where a.id=b.id) 以上查询使用了exists语句,exists()会执行A.length次,它并不缓存exists()结果集,因为exists()结果集的内容并不重要,重要的是结果集中是否有记录,如果有则返回true,没有则返回false. 它的查询过程类似于以下过程 List resultSet=[]; Array A=(select * from A) for(int i=0;i<A.length;i++) { if(exists(A[i].id) { //执行select 1 from B b where b.id=a.id是否有记录返回 resultSet.add(A[i]); } } return resultSet; 结论2:exists()适合B表比A表数据大的情况 当B表比A表数据大时适合使用exists(),因为它没有那么遍历操作,只需要再执行一次查询就行. 如:A表有10000条记录,B表有1000000条记录,那么exists()会执行10000次去判断A表中的id是否与B表中的id相等. 如:A表有10000条记录,B表有100000000条记录,那么exists()还是执行10000次,因为它只执行A.length次,可见B表数据越多,越适合exists()发挥效果. 再如:A表有10000条记录,B表有100条记录,那么exists()还是执行10000次,还不如使用in()遍历10000*100次,因为in()是在内存里遍历比较,而exists()需要查询数据库,我们都知道查询数据库所消耗的性能更高,而内存比较很快. 当A表数据与B表数据一样大时,in与exists效率差不多,可任选一个使用. In适合内外表都很大的情况，exists适合外表结果集很小的情况。 In和exists对比： 若子查询结果集比较小，优先使用in，若外层查询比子查询小，优先使 用exists。因为若用in，则Oracle会优先查询子查询，然后匹配外层查询， 若使用exists，则oracle会优先查询外层表，然后再与内层表匹配。最优化 匹配原则，拿最小记录匹配大记录 2、关于在 Oracle8i 之后 时代中in和exists的区别 in 是把外表和内表作hash join，而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列的索引。 带in的关联子查询是多余的，因为in子句和子查询中相关的操作的功能是一样的。如： select staff_name from staff_member where staff_id in (select staff_id from staff_func where staff_member.staff_id=staff_func.staff_id); 为非关联子查询指定exists子句是不适当的，因为这样会产生笛卡乘积。如： select staff_name from staff_member where staff_id exists (select staff_id from staff_func); not in 和not exists 如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引； 而not extsts 的子查询依然能用到表上的索引。 所以无论哪个表大，用not exists都比not in要快。 尽量不要使用not in子句。使用minus 子句都比not in 子句快，虽然使用minus子句要进行两次查询： select staff_name from staff_member where staff_id in (select staff_id from staff_member minus select staff_id from staff_func where func_id like '81%'); in 与 "=" 的区别 select name from student where name in ('zhang','wang','li','zhao'); 与 select name from student where name='zhang' or name='li' or name='wang' or name='zhao' 的结果是相同的。 3、关于在 Oracle8i 之后 时代中in和exists的区别 在ORACLE 11G大行其道的今天，还有很多人受早期版本的影响，记住一些既定的规则， 1.子查询结果集小，用IN 2.外表小，子查询表大，用EXISTS 这是完全错误的观点。在8i时代，这经常是正确的，但是现在已经11G了，马上12C就要面世了。其实在ORACLE 9i CBO就已经优化了IN,EXISTS的区别，ORACLE优化器有个查询转换器，很多SQL虽然写法不同，但是ORACLE优化器会根据既定规则进行查询重写，重写为优化器觉得效率最高的SQL，所以可能SQL写法不同，但是执行计划却是完全一样的。 IN与EXISTS有一点要记住：IN一般是用于非相关子查询，而EXISTS一般用于相关子查询。当然IN也可以用于相关子查询，EXISTS也可以用于非相关子查询。但是这区别很重要，虽然优化器很强大，但是查询转换是有一定的限制的，在EXISTS性能低下，无法进行相关查询转换，比如不能UNNEST SUBQUERY,那么可能我们需要改写SQL，通常可以用IN/JOIN等改写相关子查询，或对于含有OR的SEMI JOIN改为UNION ALL/UNION的形式。 下面就用例子说明一下： DROP TABLE a; DROP TABLE b; CREATE TABLE a AS SELECT * FROM hr.employees; CREATE TABLE b AS SELECT * FROM hr.employees; --反复插入，构造20万行+ INSERT INTO a SELECT * FROM a; INSERT INTO b SELECT * FROM a; COMMIT； BEGIN dbms_stats.gather_table_stats(ownname => USER,tabname => 'a',estimate_percent => 100,cascade => TRUE); dbms_stats.gather_table_stats(ownname => USER,tabname => 'b',estimate_percent => 100,cascade => TRUE); END; / 1.测试IN,EXISTS在简单查询中，是等价的 SQL> set autotrace traceonly exp SQL> SELECT * FROM a 2 WHERE EXISTS( 3 SELECT 1 FROM b WHERE a.employee_id=b.employee_id); 执行计划 ---------------------------------------------------------- Plan hash value: 2317816356 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 217K| 15M| 1375 (2)| 00:00:17 | |* 1 | HASH JOIN | | 217K| 15M| 1375 (2)| 00:00:17 | | 2 | SORT UNIQUE | | 217K| 848K| 126 (1)| 00:00:02 | | 3 | INDEX FAST FULL SCAN | IDX2_B | 217K| 848K| 126 (1)| 00:00:02 | | 4 | TABLE ACCESS FULL | A | 217K| 14M| 620 (1)| 00:00:08 | --------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("A"."EMPLOYEE_ID"="B"."EMPLOYEE_ID") SQL> SELECT * FROM a 2 WHERE a.employee_id IN (SELECT b.employee_id FROM b); 执行计划 ---------------------------------------------------------- Plan hash value: 2317816356 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 217K | 15M| 1375 (2) | 00:00:17 | |* 1 | HASH JOIN | | 217K | 15M| 1375 (2) | 00:00:17 | | 2 | SORT UNIQUE | | 217K | 848K| 126 (1) | 00:00:02 | | 3 | INDEX FAST FULL SCAN | IDX2_B | 217K | 848K| 126 (1) | 00:00:02 | | 4 | TABLE ACCESS FULL | A | 217K | 14M| 620 (1) | 00:00:08 | --------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("A"."EMPLOYEE_ID"="B"."EMPLOYEE_ID") 可以看出，两个计划完全没有区别。类似于ORACLE查询重写为： SELECT a.* FROM a,(SELECT DISTINCT b.employee_id FROM b) b1 WHERE a.employee_id=b1.employee_id; 结语：讨论SQL语句：select * from A where id in (1,2,3,4,5,6,7,8,9) 中 在Oracle8i 版本之后的Oracle数据库中性能是一样的。