Oracle 子查询

Oracle 子查询

子查询可以返回单行结果，可以返回多行结果，也可以不返回结果。

 

如果子查询未返回任何行，则主查询也不会返回任何结果

(空值)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'

的结果是相同的。