LYWILL设计运营 - 网站运营与推广、开发技术、成功项目展示。

Oracle 子查询

本文总结了Oracle子查询的用法,刚刚用到了:“IN是不对NULL进行处理”。
 

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

 

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

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

的结果是相同的。

标签: oracle

作者:lywill 分类:开发技术 浏览:2518 评论:0