Oracle--子查询(单行子查询、多行子查询、相关子查询)详解

什么是子查询？

使用子查询解决问题：谁的工资比SCOTT高？

子查询的语法

• $子查询（内查询）在主查询之间一次执行完成。$
• $子查询的结果被主查询使用（外查询）。$

子查询的类型

单行子查询

• 只返回一条记录
• 单行操作符

操作符	含义
=	Equal to
>	Greater than
>=	Greater than or equal to
<	Less than
<=	Less than or equal to
<>	Not equal to

示例1：

select ename,job,sal
from emp
where job=
      (select job
      from emp
      where empno=7566) and sal >
            (select sal
            from emp
            where empno=7782);

示例2：

select ename,job,sal
from emp
where sal=
     (select min(sal)
     from emp);

示例3：

select deptno,min(sal)
from emp
group by deptno
having min(sal) >
      (select min(sal)
      from emp
      where deptno=20);

示例4：
查询出比雇员7654的工资高，同时从事和7788的工作一样的员工

select *
from emp t1
where t1.sal>
      (select t.sal 
      from emp t 
      where t.empno=7654) and t1.job=
            (select t2.job
            from emp t2
            where t2.empno=7654);

示例5：
要求查询每个部门的最低工资和最低工资的雇员和部门名称

 select d.dname, a.minsal, e.ename
 from dept d,
      (select deptno, min(sal) minsal 
      from emp
      group by deptno) a,emp e
 where d.deptno=a.deptno and e.sal=a.minsal;

非法使用单行子查询示例

select empno,ename
from emp
where sal=
     (select min(sal)
     from emp
     group by deptno);

多行子查询

• 返回了多条记录
• 多行操作符

操作符	含义
IN	等于列表中的任何一个
ANY	和子查询返回的任意一个值比较
ALL	和子查询返回的所有值比较

示例：in 在集合中
查询部门名称是SALES和ACOUNTING的员工

select * from emp where deptno in
       (select deptno from dept where dname='SALES' or dname='ACCOUNTING');

示例：any 和集合中的任意一个值比较
查询工资比30号部门员工高的员工信息

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

示例：all 和集合中的所有值比较
查询工资比30号部门所有员工高的员工信息

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

子查询需要注意的问题

• 括号
• 合理的书写风格
• 可以在主查询的where、select、having、from后面使用子查询
• 不可以在group by使用子查询
• 强调from后面的子查询
• 主查询和子查询可以不是同一张表；只有子查询返回的结果 主查询可以使用 即可
• 一般不在子查询中排序；但在top-n分析问题中，必须对子查询排序
• 一般先执行子查询，再执行主查询；相关子查询列外
• 单行子查询只能使用单行操作符；多行子查询只能使用多行操作符
• 子查询中的null

示例：在select语句后使用子查询
注意：select语句后的子查询必须是单行子查询
查询员工号，姓名，薪水，7839的职位

select empno,ename,sal,
       (select job 
       from emp 
       where empno=7839)
from emp;

示例：在from语句后面使用子查询
查询员工信息：员工号，姓名，月薪

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

示例：top-n分析问题
rownum行号：

1. rownum永远按照默认的顺序生成
2. rownum只能使用小于等于，不能使用大于等于

查询员工表中工资最高的前三名

select rownum, empno, ename, sal
from (select * from emp order by sal desc)
where rownum <= 3;

示例：找到员工表中薪水大于本部门平均薪水的员工

select e.empno, e.ename, e.sal, d.avgsal
from emp e, (select deptno, avg(sal) avgsal from emp group by deptno) d
where e. deptno=d.deptno and e.sal > d.avgsal;

相关子查询：
将主查询中的值 作为参数传递给子查询
使用相关子查询解决上面的示例

select empno, ename, sal, (select avg(sal) from emp where deptno=e.deptno) avgsal
from emp e
where sal > (select avg(sal) from emp where deptno=e.deptno);

示例：统计每年入职员工的个数

select count(*) Total,
       sum(decode(to_char(hiredate, 'yyyy'), '1980', 1, 0)) "1980",
       sum(decode(to_char(hiredate, 'yyyy'), '1981', 1, 0)) "1981",
       sum(decode(to_char(hiredate, 'yyyy'), '1982', 1, 0)) "1982",
       sum(decode(to_char(hiredate, 'yyyy'), '1987', 1, 0)) "1987"
from emp;

子查询中的null值问题

• 单行子查询中的null值问题
  - 多行子查询中的null值问题
  示例：查询不是老板的员工
  查询错误，集合中有null值不可以使用not in