oracle中的exists和in⽤法详解
以前⼀直不知道exists和in的⽤法与效率，这次的项⽬中需要⽤到，所以⾃⼰研究了⼀下。下⾯是我举两个例⼦说明两者之间的效率问题。前⾔概述：
“exists”和“in”的效率问题，涉及到效率问题也就是sql优化：
1.若⼦查询结果集⽐较⼩，优先使⽤in。
2.若外层查询⽐⼦查询⼩，优先使⽤exists。原理是：若匹配到结果，则退出内部查询并将条件标志为true，传回全部结果资料
因为若⽤in，则oracle会优先查询⼦查询，然后匹配外层查询，原理是：in不管匹配到匹配不到都全部匹配完毕，匹配相等就返回true，就会输出⼀条元素.
若使⽤exists，则oracle会优先查询外层表，然后再与内层表匹配
也就是：”匹配原则，拿最⼩记录匹配⼤记录。也就是遍历的次数越少越好"
例⼦如下：
1) select * from T_USER1 where exists(select 1 from T_USER2 where T_USER1.jxb_id =T_USER2.jxb_id ) ;
T_USER1 数据量⼩⽽T_USER2 数据量⾮常⼤时，T_USER1 <<T_USER2  时，1) 的查询效率⾼。
原理解析：以上查询使⽤了exists语句,sql语句如：select a.* from A a where exists(select 1 from B b where =)
exists()会执⾏A.length次，它并不缓存exists()结果集，因为exists()结果集的内容并不重要，重要的是结果集中是否有记录，如果有则返回true，没有则返回false.
它的查询过程类似于以下过程：
1 List resultSet=[];
2 Array A=(select * from A)
3
4for(int i=0;i<A.length;i++) {        //这个循环次数越少越好
5if(exists(A[i].id) {    //执⾏select 1 from B b where b.id=a.id是否有记录返回
6        resultSet.add(A[i]);
7    }
8 }
9return resultSet;
当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()需要查询数据库，我们都知道查询数据库所消耗的性能更⾼,⽽内存⽐较很快.
2) select * from T_USER1  where T_USER1.jxb_id in (select T_USER2 .jxb_id from T_USER2 ) ;
T_USER1 数据量⾮常⼤⽽T_USER2数据量⼩时，T_USER1 >>T_USER2时，2) 的查询效率⾼。
原理解析：这⾥有条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次，遍历次数⼤⼤减少，效率⼤⼤提升.
=======================================================================================================
详解上⾯的⽤法：
exists ⽤法：
其中 “select 1 from T_USER2 where T_USER1.jxb_id =T_USER2.jxb_id” 相当于⼀个关联表查询，相当于
“select 1 from T_USER1,T_USER2  where T_USER1.jxb_id=T_USER2.jxb_id”
这种情况下不能单独执⾏select 1那部分的sql，不然会报语法错误的，这也是使⽤exists需要注意的地⽅。
“exists（xxx）”就表⽰括号⾥的语句能不能查出记录，它要查的记录是否存在。
因此“select 1”这⾥的 “1”其实是⽆关紧要的，换成“*”也没问题，它只在乎括号⾥的数据能不能查出来，是否存在这样的记录，如果存在，这 1）句的where 条件成⽴。
==============================
in 的⽤法：
“2) select * from T_USER1  where T_USER1.jxb_id in (select T_USER2 .jxb_id from T_USER2 ) ;
这⾥的“in”后⾯括号⾥的语句搜索出来的字段的内容⼀定要相对应，⼀般来说，T1和T2这两个表的a字段表达的意义应该是⼀样的，否则这样查没什么意义。注意：两个字段名称可以不同，但是代表的东西⼀定是⼀样的才可以。
打个⽐⽅：T1，T2表都有⼀个字段，表⽰⼯单号，但是T1表⽰⼯单号的字段名叫“ticketid”，T2则为“id”，但是其表达的意义是⼀样的，⽽且数据格式也是⼀样的。这时，⽤ 2）的写法就可以这样：
exists的用法1 “select * from T1 where T1.ticketid in (select T2.id from T2) ”
2
3 Select name from employee where name not in (select name from student);
4
5 Select name from employee where not exists (select name from student);
第⼀句SQL语句的执⾏效率不如第⼆句。
总结：
通过使⽤EXISTS，Oracle会⾸先检查主查询，然后运⾏⼦查询直到它到第⼀个匹配项，这就节省了时间。
Oracle在执⾏IN⼦查询时，⾸先执⾏⼦查询，并将获得的结果列表存放在⼀个加了索引的临时表中。在执⾏⼦查询之前，系统先将主查询挂起，待⼦查询执⾏完毕，存放在临时表中以后再执⾏主查询。
这也就是使⽤EXISTS⽐使⽤IN通常查询速度快的原因

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。