Oracle：子查询

例：查询工资比scott高的员工信息，是3000

首先得到soctt的工资,select sal from emp where ename=’scott’;

在查出比3000高的工资，select * from emp where sal>3000;

 

什么情况下需要子查询呢？不能一步求解的时候。例如刚才查出比3000高的工资，需要用到前一步的结果。

也就是说，将第二步的查询结果作为第一步的条件。

 

子查询就是查询语句的嵌套，在select 中嵌套另一个select。

 

--用子查询解决上面的问题。

select * from emp where sal > (selectsal from emp where ename=’scott’);

 

注意的问题：

(1)注意子查询要在()中。

(2)采用合理的书写风格。

(3)可以在主查询的where， select， having， from 后面放置子查询。

(4)不可以在group by后面放置子查询。

(5)强调from后面的子查询

(6)主查询和子查询可以不是同一张表。只要子查询返回的结果，主查询可以使用即可。

(7)一般我们不在子查询中使用order by。但在Top-N分析问题中，必须使用orderby。

(8)一般先执行子查询，再执行主查询；但相关子查询除外。

(9)单行子查询只能使用单行操作符，多行字查询只能使用多行操作符。

(10)子查询中的null。

 

--在select中放一个子查询，必须是一个单行子查询，单行子查询是子查询结果只有一条记录的查询。

select ename,sal,(select job from emp whereempno=7839) 一列 from emp;

--在having中放置子查询。与where差不多。

 

--在from后面放置一个子查询。查询员工信息，姓名，薪水

select * from (select ename,sal from emp);

--查询员工信息，姓名，薪水，年薪

Select * from (select ename,sal,sal*12annalsal from emp);

 

--主查询和子查询可以不是同一张表。

--查询部门名称是SALES的员工

Select * from emp where deptno=(selectdeptno from dept where dname=’SALES’);

--也可以通过多表查询完成，

select e.* from emp e,dept d wheree.deptno=d.deptno and d.dname=’SALES’;

--两个都可以解决，那哪个更优呢？见Oracle优化文档第6点。

 

什么是Top-N分析问题？比如找到工资最高的三个人，或者工资最低的三个人，就是Top-N问题。取出前三个，就涉及到了排序问题。在Oracle中的Top-N问题的解决是使用rownum。

 

单行子查询只返回一行，可以使用单行比较操作符：= , > , >= , < , <= , <>

--在子查询中也可以使用组函数，查询工资最低的员工信息。

Select last_name,job_id,salary fromemployees where salary=(select MIN(salary) from employees);

--子查询中的having子句，求最低工资大于50号部门的最低工资的部门号，使用了组函数，只能使用having

Select department_id,MIN(salary) fromemployees group by department_id having MIN(salary) > (select MIN(salary)from employees where department_id=50);

--子查询的错误使用，需要一个单一结果，但是返回了多条记录，报错。

Select employee_id,last_name from employeeswhere salary=(select MIN(salary) from employees group by department_id);

 

--子查询中的null问题。

--判断null用is null或is not null，如果where deptno=(子查询);子查询返回一个null，则结果永远不对，因为null != null。

--多行子查询，使用IN ,ANY , ALL

/*

IN等于列表中的任何一个

ANY和子查询返回的任意一个值比较

ALL和子查询返回的所有值比较

*/

--in，在集合中，查询部门名称是SALES和ACCOUNTING的员工

Select * from emp where deptno in (selectdeptno from dept where dname=’SALES’ or dname=’ACCOUNTING’);

 

--ANY，和集合中任意一个值比较

--查询工资比30号部门任意一个员工高的员工信息

Select * from emp where sal > any (selectsal from emp where deptno=30);

 

--ALL和集合的所有值比较

--查询工资比30号部门所有员工高的员工信息

Select * from emp where sal > all(selectsal from emp where deptno=30);

 

--not in 中不能有null的原因：

/*

比如：anot in(10,20,null)

这句话相当于a!=10 and a!=20 and a!=null;最后的a!=null永远是假，所以整个也永远是假，所以永远不成立。

而a in(10,20,null)

等价于a=10or a=20 or a=null，最后一个值为假，但是是用or连接的，所以没关系。

*/

--查询不是老板的员工,mgr为null的点

Select* from emp where empno not in(select mgr from emp where mgr is not null);mgr中有null，过滤掉。