oracle子查询详解2-bckong

yinian

　　原文出处：http://www.cnblogs.com/Johnny_Z/archive/2010/11/07/1870939.html
　　子查询可以返回单行结果，可以返回多行结果，也可以不返回结果。
　　如果子查询未返回任何行，则主查询也不会返回任何结果
　　(空值)select * from emp where sal > (select sal from emp where empno = 8888);
　　如果子查询返回单行结果，则为单行子查询，可以在主查
　　询中对其使用相应的单行记录比较运算符
　　(正常)select * from emp where sal > (select sal from emp where empno = 7566);
　　如果子查询返回多行结果，则为多行子查询，此时不允许
　　对其使用单行记录比较运算符
　　(多值)select * from emp where sal > (select avg(sal) from emp group by deptno);//非法
　　子查询中常用方法
　　1。any即任何一个。如果在where条件中加入>any，意思是大于任何一个，也就是大于最小的
　　select * from emp t
　　where t.sal>
　　any(select sal from hhgy.emp where deptno=30)
　　2。some即一些。和any的用法基本相同。用any的地方都可以用some代替。不过some大多用在=操作中。表示等于所选集合中的任何一个。当然any也可以用于=操作中，效果和some相同。
　　select * from emp t
　　where t.sal=
　　some(select sal from hhgy.emp where deptno=30)
　　3。all即所有。如果在where条件中加入>all，意思是大于每一个，也就是大于最大的。
　　select * from emp t
　　where t.sal>
　　all(select sal from hhgy.emp where deptno=30)
　　4。In
　　select * from emp t
　　where t.deptno in(30,40)
　　5。exists
　　select * from hhgy.emp where exists(select * from hhgy.emp where deptno=30)
　　如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in,反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。 其实我们区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了 另外IN是不对NULL进行处理
　　如：
　　select 1 from dual where null in (0,1,2,null)
　　为空
　　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
　　表 T1 不可避免的要被完全扫描一遍
　　in 是把外表和内表作hash join，而exists是对外表作loop，每次loop再对内表进行查询。
　　例如：表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'
　　的结果是相同的。