oracle子查询详解2-bckong
原文出处：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'
的结果是相同的。
页:
[1]