Oracle子查询

子查询：

select * from emp where sal > (select sal from emp where empno = 7499);(空值)如果子查询未返回任何行，则主查询也不会返回任何结果
select * from emp where sal > (select sal from emp where empno = 7566);(正常)果子查询返回单行结果，则为单行子查询，可以在主查询中对其使用相应的单行记录比较运算符

子查询中常用方法：

1、any即任何一个。如果在where条件中加入>any，意思是大于任何一个，也就是大于最小的
select * from emp t where t.sal > any(select sal from emp where deptno=10)
2、some即一些。和any的用法基本相同。用any的地方都可以用some代替。不过some大多用在=操作中。表示等于所选集合中的任何一个。当然any也可以用于=操作中，效果和some相同。
select * from emp t where t.sal > some(select sal from emp where deptno=10)
3、all即所有。如果在where条件中加入>all，意思是大于每一个，也就是大于最大的。
select * from emp t where t.sal > all(select sal from emp where deptno=20)
4、in
select * from emp t where t.deptno in (10,20);
select * from A where id in(select id from B)
in()适合B表比A表数据小的情况
5、exists
select * from emp where exists(select * from emp where deptno=30)
select a.* from A a where exists(select 1 from B b where a.id=b.id)
exists()适合B表比A表数据大的情况
6、关联子查询和非关联子查询：关联子查询的子查询不能独立运行，其结果集会受到主查询的影响.非关联子查询可以独立运行不受影响.
非关联子查询：select e.ename, e.sal from emp e where e.deptno = (select d.deptno from dept d where d.dname = 'SALES');
关联子查询：select e.ename, e.sal,(select d.dname from dept d where d.deptno = e.deptno) dname from emp e;

Test：查询时领导的员工，select a.empno, a.ename from emp a where exists(select 1 from emp b where a.empno = b.mgr);

7、ROWNUM使用
1）取出前五条记录：select e.*, rownum from emp e where rownum <= 5; 但是，如果对一个排序结果取前N条数据的话，使用ROWNUM存在一些“陷阱”
2）查询大于N的记录，必须使用别名：select * from (select e.*, rownum rn from emp e) where rn > 5;
Test：查询薪水前三名，select * from (select * from emp e order by e.sal desc) where rownum <= 3;
3）分页：select * from (select e.*, rownum rn from emp e) where rn between 5 and 10;
4）优化性能：select * from emp where sal < 2000 and rownum = 1;
5）ROWNUM的使用“陷阱”：不能对ROWNUM使用>（大于1的数值）、>=（大于或等于1的数值）、=（大于或等于1的数值），否则无结果。
这是因为：
① ROWNUM是伪列，必须要要有返回结果后，每条返回记录就会对应产生一个ROWNUM数值；
② 返回结果记录的ROWNUM是从1开始排序的，因此第一条始终是1;
这样，当查询到第一条记录时，该记录的ROWNUM为1，但条件要求ROWNUM>1，因此不符合，继续查询下一条；因为前面没有符合要求的记录，因此下一条记录过来后，其ROWNUM还是为1，如此循环，就不会产生结果，上述查询可以通过子查询来替代。
6）  ROWNUM和Order BY：在使用ROWNUM时，只有当Order By的字段是主键时，查询结果才会先排序再计算ROWNUM，但是，对非主键字段OBJECT_NAME进行排序时，结果就混乱了。原因是：oracle先按物理存储位置（rowid）顺序取出满足rownum条件的记录，即物理位置上的前5条数据，然后在对这些数据按照Order By的字段进行排序，而不是我们所期望的先排序、再取特定记录数。非主键排序可采用子查询：select * from (select * from emp order by ename) where rownum < 5;