Oracle之select查询语句
⼀、⼦查询
⼀个查询的条件包含另⼀个查询的结果
⼦查询⼀般出现在SELECT语句的
WHERE
⼦句中，Oracle也⽀持在
FROM
或
HAVING
⼦句中出现⼦查询。
⼦查询⽐主查询先执⾏
，
结果作为主查询的条件，在书写上要⽤圆括号扩起来，并放在⽐较运算符的右侧。⼦查询可以嵌套使⽤，最⾥层的查询最先执⾏。⼦查询可以在SELECT、INSERT、UPDATE、DELETE
等语句中使⽤。
⼦查询按照返回数据的类型可以分为
单⾏⼦查询、多⾏⼦查询和多列⼦查询
。
1.如果⼦查询返回单⾏结果，则为单⾏⼦查询，可以在主查询中对其使⽤相应的
单⾏
记录⽐较运算符
查询⽐SCOTT⼯资⾼的雇员名字和⼯资
SELECT ename, sal FROM employee
WHERE sal>(SELECT sal FROM employee WHERE empno=7788);
2.
如果⼦查询未返回任何⾏，则主查询也不会返回任何结果
(
空值select语句查询日期
)select * from emp where sal > (select sal from emp where empno = 8888); //empno的值不存在8888所以返回空值
3.如果⼦查询返回多⾏结果，
则为多⾏⼦查询，此时不允许对其使⽤单⾏记录⽐较运算符(>,<,=等)，可以使⽤下⾯的常⽤⽅法解决。
(多值)select * from emp where sal > (select avg(sal) from emp group by deptno);
//⾮法
查询雇员表中排在第6～9位置上的雇员
SELECT ename,sal FROM (SELECT rownum as num,ename,sal FROM employee WHERE rownum<=9 )
WHERE num>=6;
说明：⼦查询出现在FROM从句中，检索出⾏号⼩于等于9的雇员，并⽣成num编号列。在主查询中检索⾏号⼤于等于6的雇员。
注意：以下⽤法不会有查询结果
SELECT ename,sal FROM employee WHERE rownum>=6 AND rownum<=9 //因为rownum从1开始
⼦查询中常⽤⽅法（⽤于多⾏⼦查询跟多列⼦查询）
1。
any
即任何⼀个。如果在where条件中加⼊>any，意思是⼤于任何⼀个，也就是⼤于最⼩的；<any为⼩于最⼤的；=any⽆意义⼀般不写。
select * from emp t
where t.sal >
any(select sal p where deptno=30)
2。
some
即⼀些。和any的⽤法基本相同。⽤any的地⽅都可以⽤some代替。不过some⼤多⽤在=操作中。表⽰等于所选集合中的任何⼀个。当然any也可以⽤于=操作中，效果和some 相同。
select * from emp t
where t.sal =
some(select sal p where deptno=30)
3。
all
即所有。如果在where条件中加⼊>all，意思是⼤于每⼀个，也就是⼤于最⼤的；<all为⼩于最⼩的；=all⽆意义⼀般不写。
select * from emp t
where t.sal >
all(select sal p where deptno=30)
4。
In
意思是在某些数据之中
select * from emp t
where t.deptno in(30,40);
-----deptno在30跟40之中等同于30 or 40
4.1 使⽤in作多列⼦查询
select ename, job, sal from emp where (sal,job) in (select max(sal), job from emp group by job);——多列⼦查询
5。
exists
主查询存在exists后⾯的条件。将主查询的数据放到⼦查询中做条件验证，根据验证结果(true或false)来决定主查询数据结果是否保留(true保留)
select * p where exists(select * p where deptno=30);
5.1not exists 主查询不存在not exists后⾯的条件。结果与exists相反
select * p where not exists(select * p 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 )
效率⾼，⽤到了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 )
效率低，⽤到了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'
的结果是相同的。
⼀/2.
分页查询
SELECT
*
FROM
(
SELECT
A.
*
, ROWNUM RN
FROM
(
SELECT
*
FROM
TABLE_NAME) A
WHERE
ROWNUM
<=
40
)
WHERE
RN
>=
21
//分页查询加上按照id倒序排列
SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE_NAME ORDER BY "ID" DESC) A WHERE ROWNUM <= 40 ) WHERE rn >= 21
说明：
例⼦中先在内层通过（ROWNUM <= 40）来控制最⼤值查询出页码最⼤范围内所有的结果，然后在外层控制最⼩值（ROWNUM >= 21）查询出⼤于最⼩值的结果
SELECT
*
FROM
(
SELECT
A.
*
, ROWNUM RN
FROM
(
SELECT
*
FROM
TABLE_NAME) A )
WHERE

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