子查询就是将一个查询的结果作为另一个查询的数据来源或判断条件的查询。
常见的子查询包括：WHERE子查询，FROM子查询，HAVING子查询，EXISTS子查询，子查询必须使用小括号括起来。

WHERE子查询

案例
查询出低于平均工资的雇员信息（子查询返回的结果集是单行单列）

SELECT * FROM emp WHERE sal<(SELECT AVG(sal) FROM emp);

查询出公司最早入职的雇员信息

SELECT * FROM emp WHERE hiredate=(SELECT MIN(hiredate) FROM emp);

查询出与SCOTT从事统一工作且工资相同的雇员信息
若子查询中返回的是单行多列的结果集
主查询中的条件判断字段的顺序要与子查询中需要查询的字段顺序保持一致

SELECT * FROM emp WHERE (job,sal)=
(SELECT job,sal FROM emp WHERE ename='SCOTT')
 AND ename<>'SCOTT'; 

查询出与经理职位工资相同的员工的工资
子查询中返回的结果集是多行单列

SELECT * FROM emp WHERE sal IN(SELECT sal FROM emp WHERE job='MANAGER')
AND job<>'MANAGER';

ANY 、ALL关键字
ANY表示范围内任意一个，ALL表示范围内全部
查询出薪资比经理的最高薪资低的员工信息(即小于任意一个经理的薪资)

SELECT * FROM emp WHERE sal<ANY(SELECT sal FROM emp WHERE job='MANAGER');

查询出比经理的最低薪资低的员工信息(即小于所有的经理的薪资)

SELECT * FROM emp WHERE sal<ALL(SELECT sal FROM emp WHERE job='MANAGER');

查询出薪资排名第三的雇员的信息

SELECT * FROM emp
 WHERE sal=(SELECT MAX(sal) FROM emp WHERE sal<(SELECT max(sal) FROM emp WHERE sal<(SELECT MAX(sal) FROM emp)));

HAVING子查询

HAVING子查询指的是分组过滤条件下的子查询，在HAVING子句中。
案例
查询出平均薪资高于所有员工平均薪资的职位的名称，以及职位的人数，以及这些职位的平均薪资

SELECT job,COUNT(*),AVG(sal) FROM emp
GROUP BY job HAVING AVG(sal)>
(SELECT AVG(sal) FROM emp);

查询出平均薪资最高的职位的名称和该职位的平均薪资

SELECT job,AVG(sal) FROM emp
GROUP BY job HAVING AVG(sal) = 
(SELECT MAX(AVG(sal)) FROM emp GROUP BY job);

FROM子查询

FROM子查询的数据来源是另一个查询的结果相当于一张临时数据表，
通常为子查询定义别名，如果要用子查询中的字段使用别名.字段名调用，
返回的一般是多行多列的结果集
案例
查询平均工资高于2000的职位名称以及该职位的平均工资

SELECT job,avgsal
 FROM (SELECT job,AVG(sal) AS avgsal FROM emp GROUP BY job)temp
 WHERE temp.avgsal>2000;

查询出有佣金并且薪资高于佣金的雇员的信息

SELECT * FROM (SELECT * FROM emp WHERE comm IS NOT NULL)
WHERE sal>comm;

查询各岗位的最高薪水、最低薪水，要求只统计薪水>1000的；

SELECT job,MAX(sal),MIN(sal)
 FROM (SELECT * FROM emp WHERE sal>1000)
 GROUP BY job;

查询各部门的平均薪水及部门编号，只统计薪水>2000的

SELECT AVG(sal),deptno
 FROM (SELECT * FROM emp WHERE sal>2000)
  GROUP BY deptno;

查询各部门的平均薪水及部门编号，要求只有员工姓名包含’A’的才参与统计，只列出平均薪水>1500的，按照平均薪水降序排列

SELECT AVG(sal) avgsal,deptno FROM
 (SELECT * FROM emp WHERE ename LIKE '%A%')
 GROUP BY deptno HAVING AVG(sal) > 1500 ORDER BY avgsal DESC;

查询最高薪水的员工信息

SELECT * FROM emp WHERE sal=(SELECT MAX(sal) FROM emp);

查询薪水大于所在部门平均薪水的员工信息

SELECT * FROM emp e,
 (SELECT deptno,AVG(sal) avgsal 
 FROM emp GROUP BY deptno)t     
 WHERE e.deptno=t.deptno AND sal>avgsal;

查询各部门最高薪水的员工信息

SELECT * FROM emp e,
(SELECT deptno,MAX(sal) maxsal FROM emp GROUP BY deptno) t 
 WHERE e.deptno=t.deptno AND e.sal=t.maxsal;

EXISTS子查询

EXISTS子查询的特征：将主查询中的数据带到子查询中验证，若验证成功则返回TRUE，验证失败返回FALSE；当主查询接收到TRUE时，该数据显示，若接收到FALSE，该数据不显示。
案例
查询出有部门的雇员信息

SELECT * FROM emp e WHERE EXISTS (SELECT * FROM dept d
 WHERE e.deptno=d.deptno);

查询出没有佣金的雇员的信息

SELECT * FROM emp e WHERE EXISTS(
 SELECT * FROM emp p WHERE e.comm=p.comm);

什么是子查询？

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

子查询的语法

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

子查询的类型

单行子查询

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

示例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 在集合中
查询部门名称是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