子查询
1.1、定义
指嵌入在其他sql语句中的select语句
通俗的来讲指的是在一个查询之中嵌入若干个小的查询
子查询可以出现在查询语句的(9i中除ORDER BY语句)任意位置上，但是实际开发运用中，子查询出现在WHERE和FROM子句之中较多。
1.2 、核心作用
子查询的出现主要是为了解决多表查询之中的性能问题。
1.3 、分类
嵌套查询(标准子查询)
指子查询可以脱离主查询独立执行关联查询(相关子查询)
关联子查询就是指子查询与主查询之间有条件关联,关联子查询会引用外部查询中的一列或多列。这种子查询之所以被称为关联子查询，是因为子查询的确与外部查询有关。当问题的答案需要依赖于外部查询中包含的每一行中的值时，通常就需要使用关联子查询。
笔记：关联子查询（相关子查询）：子查询不可以脱离主查询独立执行
1.4 、完整的语法格式
SELECT [DISTINCT] *|分组字段1 [别名] [,分组字段2 [别名] ,…] | 单行函数 | 统计函数 |(
SELECT [DISTINCT] *|分组字段1 [别名] [,分组字段2 [别名] ,…] | 统计函数
FROM 表名称 [别名], [表名称 [别名] ,…]
[WHERE 条件(s)]
[GROUP BY 分组字段1 [,分组字段2 ,…]]
[HAVING 分组后的过滤条件（可以使用统计函数）]
[ORDER BY 排序字段 ASC | DESC [,排序字段 ASC | DESC]])
FROM 表名称 [别名], [表名称 [别名] ,…] |(
SELECT [DISTINCT] *|分组字段1 [别名] [,分组字段2 [别名] ,…] | 统计函数
FROM 表名称 [别名], [表名称 [别名] ,…]
[WHERE 条件(s)]
[GROUP BY 分组字段1 [,分组字段2 ,…]]
[HAVING 分组后的过滤条件（可以使用统计函数）]
[ORDER BY 排序字段 ASC | DESC [,排序字段 ASC | DESC]])
[WHERE 条件(s) (
SELECT [DISTINCT] *|分组字段1 [别名] [,分组字段2 [别名] ,…] | 统计函数
FROM 表名称 [别名], [表名称 [别名] ,…]
[WHERE 条件(s)]
[GROUP BY 分组字段1 [,分组字段2 ,…]]
[HAVING 分组后的过滤条件（可以使用统计函数）]
[ORDER BY 排序字段 ASC | DESC [,排序字段 ASC | DESC]]) ]
[GROUP BY 分组字段1 [,分组字段2 ,…]]
[HAVING 分组后的过滤条件（可以使用统计函数） (
SELECT [DISTINCT] *|分组字段1 [别名] [,分组字段2 [别名] ,…] | 统计函数
FROM 表名称 [别名], [表名称 [别名] ,…]
[WHERE 条件(s)]
[GROUP BY 分组字段1 [,分组字段2 ,…]]
[HAVING 分组后的过滤条件（可以使用统计函数）]
[ORDER BY 排序字段 ASC | DESC [,排序字段 ASC | DESC]]) ]
[ORDER BY 排序字段 ASC | DESC [,排序字段 ASC | DESC]];
1.5、子查询的使用原则
在查询中根据返回的结果有以下几种情况
1、单行子查询（子查询返回的结果是单行单列）
2、多行子查询（子查询返回多行单列）
3、多列子查询（子查询返回多列，可以是单行、多行或者不返回任何结果）如果返回结果是单行多列，则可以使用单行比较运算符
子查询可以出现在操作符的左边或者右边
嵌套查询先执行，然后将结果传递给主查询。
1.6、在WHERE中使用子查询
1.6.1、说明
WHERE 的主要功能是控制数据行，那么在 WHERE 子句之中，子查询的返回结果一般情况是(
单行单列
多行单列
单行多列 ) 的数据
1.6.2、子查询返回单行单列
如果返回单行单行单列可以使用单行操作符: >、>=、 <、 <= 、<>!=、=
1、查询出工资比SCOTT还要高的全部雇员信息
查询SCOTT的工资是多少：
SELECT sal FROM emp WHERE ename='SCOTT';过滤数据
SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='SCOTT');
2、查询和SMITH职位相同的所有员工的员工姓名和职位
查询出SMITH的职位
SELECT job FROM emp WHERE ename='SMITH'过滤数据显示员工的姓名和职位
SELECT ename,job FROM emp WHERE job=(SELECT job FROM emp WHERE ename='SMITH')
3、查询出高于公司平均工资的全部员工信息
公司的平均工资应该使用AVG()函数求出。
SELECT AVG(sal) FROM emp;过滤数据显示全部员工的信息
SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp);
4、查询员工姓名ADAMS相同职位的员工信息 并且薪资大于员工姓名CLARK的薪资的员工信息
查询员工姓名ADAMS的职位
SELECT job FROM emp WHERE ename='ADAMS'查询员工姓名CLARK的薪资
SELECT sal FROM emp WHERE ename='CLARK'过滤数据
SELECT * FROM emp WHERE job=(SELECT job FROM emp WHERE ename='ADAMS') AND sal>(SELECT sal FROM emp WHERE ename='CLARK')
1.6.3、子查询返回单列多行
如果返回的多行单列必须使用多行比较操作符
| 操作符 | 说明 |
|---|---|
| [NOT] IN | (不)等于列表中的任何一个 |
| ALL | 和子查询返回的所有值比较 |
| exists | 检查子查询中是否存在满足条件的记录,返回结果集为真 |
| ANY | 与子查询返回的任意一个值比较 |
1.6.3.1、IN操作符
注意：
如果使用NOT IN操作，在子查询之中，如果有一个内容是null，则不会查询出任何的结果。
解决非空问题，使用空判断NVL。
查询各个职位中工资最高的员工信息(子查询是分组查询)
SELECT ename, job, sal FROM emp WHERE sal in (SELECT max(sal) FROM emp GROUP BY job)查询员工工资跟管理部相同的员工工资
SELECT * FROM emp WHERE sal IN (SELECT sal FROM emp WHERE job='MANAGER');
1.6.3.2、ANY
1、使用方式
<any：小于最大的
>any：大于最小的
=ANY：功能与IN操作符是完全一样的
2、示例代码
查询工资高于任何部门的平均工资的员工信息
select * from emp where sal>any(select avg(sal) from emp group by deptno)询工资低于任何部门的平均工资的员工信息(使用<any)
select * from emp where sal < any (select min(sal) from emp group by deptno);
1.6.3.3、ALL
1、使用方式
<ALL：比子查询返回的最小值还要小
>ALL：比子查询返回的最大值还要大
注意：ALL(如果有一个内容是null，则不会查询出任何的结果)
2 示例代码
查询工资高于所有部门的平均工资的员工(>ALL)
select * from emp where sal>all(select avg(sal) from emp group by deptno)
查询工资小于所有部门的平均工资的员工不包括(<ALL)
select * from emp where sal<all(select avg(sal) from emp group by deptno)
1.6.3.4、EXISTS 操作符
1、检查子查询中是否存在满足条件的记录，如果存在满足条件的记录，就不会在子查询中继续寻找，直接返回true；否则，返回false,继续在子查询中寻找。
2、示例代码
查询是管理员的员工编号,姓名,职位,部门编号信息(关联查询 EXISTS关键字)
SELECT empno, ename, job, deptno FROM emp e WHERE EXISTS (SELECT * FROM emp WHERE mgr = e.empno);
1.6.4、子查询返回单行多列
返回结果在内存中构成一个单行多列的数据表，返回单行多列的子查询在实际应用中与返回单行单列的数据类似，只是查询条件可以扩展成多个，用括号把查询条件括起来
语法格式:
where (列名,列名...) in (值1，值2...) --值 来自于子查询
1、查询sal和comm和empno为7698相同的人员的信息
先查询sal和comm和empno为7698的信息
select * from emp where (sal,comm)=(SELECT sal,comm FROM emp WHERE empno=7698);过滤数据
select * from emp where (sal,comm)=(SELECT sal,comm FROM emp WHERE empno=7698);
1.6.5、子查询返回多行多列(了解)
语法格式：
where (列名，列名，列名...) in (值1，值2，值3...)
查询部门中员工每个职位,工资最高的员工的姓名 ,职位还有工资
select ename, job, sal from emp where (sal,job) in (select max(sal), job from emp group by job);
1.7、FROM中使用子查询
1.7.1、说明
如果在 FROM 子句里面出现的子查询，其返回的结果一般都是多行多列数据(相当于表)。
1.7.2、示例代码
范例：查询出每个部门的编号、名称、位置、部门人数、平均工资
1、方式一
SELECT d.deptno,d.dname,d.loc,COUNT(e.empno),AVG(e.sal) FROM emp e,dept d WHERE e.deptno(+)=d.deptno GROUP BY d.deptno,d.dname,d.loc;2、方式二:通过子查询完成，所有的统计查询只能在GROUP BY中出现，所以在子查询之中负责统计数据，而在外部的查询之中，负责将统计数据和dept表数据相统一。
SELECT d.deptno,d.dname,d.loc,temp.count,temp.avg FROM dept d,(SELECT deptno dno,COUNT(empno) count,AVG(sal) avg FROM emp GROUP BY deptno) temp WHERE d.deptno=temp.dno(+);3、说明
方式一的数据量，
积的数量：emp 的 14 条 * dept 的 4 条 = 56 条数据；
方式二的数据量
子查询中统计的记录是14条记录，最终统计的显示结果是3条记录；
dept表之中一共有4条记录；
如果现在产生笛卡尔积的话只有12条记录，再加上子查询中雇员的14条记录，一共才26条记录；
通过如上的分析，可以发现，使用子查询的确要比使用多表查询更加节省性能，所以在开发之中子查询出现是最多的，而且在给出一个不成文的规定：大部分情况下，如果最终的查询结果之中需要出现SELECT子句，但是又不能直接使用统计函数的时候，就在子查询中统计信息，即：有复杂统计的地方大部分都需要子查询。
查询比本部门平均工资高的员工的姓名,部门编号,工资及平均工资
SELECT e.ename, e.sal, e.deptno, temp.salavg FROM emp e, (SELECT department_id, AVG(sal) salavg FROM employees GROUP BY department_id) temp WHERE e.deptno = temp.deptno AND e.sal > temp.salavg查询出部门名称，部门的员工数，部门的平均工资，部门的最低收入雇员的姓名
SELECT d.dname,temp.cou,temp.avg,e.ename FROM (SELECT deptno,count(1) cou,round(avg(sal)) avg,min(sal) min FROM emp GROUP BY deptno ) temp, dept d, emp e WHERE d.deptno=temp.deptno AND e.sal=temp.min;
1.8、在 HAVING 子句之中使用子查询
如果使用了 HAVING 子句一定意味着进行了分组，而且进行了统计查询。在 HAVING 之中出现的子查询只能够返回 单行单列的数据
示例代码
查询出高于公司平均工资的部门编号、平均工资
1、计算出公司的平均工资
SELECT AVG(sal) FROM emp ;2、要在分组之后进行过滤
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal)>(SELECT AVG(sal) FROM emp);查询出平均工资最低的职位信息、人数、平均工资
1、找到平均工资最低的职位数据
SELECT MIN(AVG(sal)) FROM emp GROUP BY job ;2、统计函数嵌套之后无法再出现分组字段，所以现在可以将以上单行单列的结果，出现在 HAVING 子句之中
SELECT job,AVG(sal) FROM emp GROUP BY job HAVING AVG(sal)=(SELECT MIN(AVG(sal)) FROM emp GROUP BY job)查询出部门名称，部门的员工数，部门的平均工资，部门的最低收入雇员的姓名
SELECT d.dname,temp.cou,temp.avg,e.ename FROM (SELECT deptno,count(1) cou,round(avg(sal)) avg,min(sal) min from scott.emp GROUP BY deptno) temp, dept d, emp e WHERE d.deptno=temp.deptno AND e.sal=temp.min;
1.9、综合练习
查询部门名称，部门的员工数，部门的平均工资，部门的最低收入员工的姓名和最高收入员工的姓名
1、查询出部门名称，部门员工数，部门平均工资，部门最低收入和最高收入
SELECT count(*) 员工人数, avg(sal),部门平均工资 min(sal)最低工资, max(sal) 最高工资 FROM emp GROUP BY deptno;2、查询最低收入者的姓名
SELECT e.ename
FROM emp e, (SELECT deptno,
count(*),
avg(sal),
min(sal) min_sal,
max(sal) max_sal
FROM emp
GROUP BY deptno) t
WHERE e.sal = t.min_sal;
3、查询最高收入者的姓名
SELECT e.ename
FROM emp e, (SELECT deptno,
count(*),
avg(sal),
min(sal) min_sal,
max(sal) max_sal
FROM emp
GROUP BY deptno) t
WHERE e.sal = t.max_sal;
4、跟第一次查询出来的部门最高收入和最低收入同时关联两张emp 表，分别获取最高收入者和最高收入者的姓名
SELECT DEPT.DNAME 部门名称,
TEMP.C 人数,
TEMP.A 平均工资,
TEMP.M 最低工资,
TEMP.MS 最高工资,
E.ENAME 最低工资人,
DD.ENAME 最高工资人
FROM (SELECT DEPTNO,
COUNT(*) C,
AVG(SAL) A,
MIN(SAL) M,
MAX(SAL) MS
FROM EMP
GROUP BY DEPTNO) TEMP,
DEPT,
EMP E,
EMP DD
WHERE TEMP.DEPTNO = DEPT.DEPTNO
AND E.SAL = TEMP.M
AND E.DEPTNO = TEMP.DEPTNO
AND DD.SAL = TEMP.MS
AND DD.DEPTNO = TEMP.DEPTNO