例:查询工资比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,过滤掉。