Oracle数据库——子查询-07

子查询（核心，难点）

子查询没有任何固定的语法，因为子查询是一个综合体，将之前所学习过的限定查询，多表查询，分组统计

查询融合在一起形成新的查询方式，而且在开发之中子查询的使用是最多的，一定会用的

如果非要给子查询的语法，那么可以参考如下形式：

SELECT [DISTINCT] * | 列名称 [别名],...|统计函数|分组字段  

,(SELECT [DISTINCT] * | 列名称 [别名],...|统计函数|分组字段  

FROM 表名称 [别名]    

WHERE 限定条件(s)

[GROUP BY 分组字段，分组字段,..]    ==<字段数据有重复

[HAVING 分组后过滤]

[ORDER BY 排序字段[ASC|DESC],排序字段[ASC|DESC],...];  )

FROM 表名称 [别名]    

WHERE 限定条件(s)

[GROUP BY 分组字段，分组字段,..]    ==<字段数据有重复

SELECT [DISTINCT] * | 列名称 [别名],...|统计函数|分组字段  

FROM 表名称 [别名]    

WHERE 限定条件(s),(SELECT [DISTINCT] * | 列名称 [别名],...|统计函数|分组字段  

FROM 表名称 [别名]    

WHERE 限定条件(s)

[GROUP BY 分组字段，分组字段,..]    ==<字段数据有重复

[HAVING 分组后过滤]

[ORDER BY 排序字段[ASC|DESC],排序字段[ASC|DESC],...];  )

[GROUP BY 分组字段，分组字段,..]    ==<字段数据有重复

[HAVING 分组后过滤]

[ORDER BY 排序字段[ASC|DESC],排序字段[ASC|DESC],...];  

[HAVING 分组后过滤]

[ORDER BY 排序字段[ASC|DESC],排序字段[ASC|DESC],...];  

理论上子查询可以出现在一条SQL语句的任意位置之中，但是必须使用“()”声明，而一般在开发之中以下

几个子句出现子查询是比较常见

• WHERE子句：子查询一般返回单行单列，单行多列，多行单列数据：
• HAVING子句：子查询返回单行单列而且要使用统计函数；
• FROM子句：子查询返回多行多列数据
• 在WHERE子句之中使用子查询

WHERE子句之中出现的在查询表示子查询返回的单行单列，多行单列，单行多列三种数据

1.子查询返回单行单列

范例：查出公司工资最低的雇员信息

第一步：找到公司的最低工资，依靠MIN（）函数统计，不需要GROUP BY

SELECT MIN(sal) FROM emp;

第二步：以上的查询返回有单行单列的结构

SELECT * FROM emp

WHERE sal=(SELECT MIN(sal) FROM emp);

范例：查出公司雇佣最早的雇员信息

第一步：找出最早的雇员日期是多少

SELECT MIN(hiredate) FROM emp;

第二步：以上的查询返回有单行单列的数据，根据需求肯定在WHERE之中使用

SELECT * FROM emp

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

2.子查询返回单行多列（了解）

范例：查询出与SCOTT工作相同，工资相同的雇员信息

第一步，找到SCOTT的工作和工资

SELECT job,sal FROM emp WHERE ename='SCOTT';

第二步：以上的结果返回的单行二列的数据

SELECT * FROM emp

WHERE (job,sal)=(

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

范例：查询公司雇佣日期最早，工资最低的雇员

第一步：统计出公司的最早雇佣日期和最低工资

SELECT MIN(hiredate),MIN(sal) FROM emp;

第二步：以上查询返回单行多列，在WHERE中使用

SELECT * FROM emp

WHERE (hiredate,sal)=(SELECT MIN(hiredate),MIN(sal) FROM emp);

3.子查询返回多行单列数据

在之前使用过IN查询，IN查询是规定了一个范围，那么如果子查询返回的是多行单列数据，实际上也是

一个范围，例如：

范例：查询所有经理的工资

SELECT sal  FROM emp WHERE job='MANAGER';

这个时候是多行单列数据，所以它就是一个数据的参考范围，而对于数据库的参考范围可以使用三种

符号进行判断：IN,ANY,ALL

(一)IN操作

此操作与之前讲解过的IN使用形式类似

SELECT sal  FROM emp WHERE job='MANAGER';

SELECT * FROM emp

WHERE sal IN (SELECT sal  FROM emp WHERE job='MANAGER');

但是需要注意的是，如果此时使用的是NOT IN，子查询如果有null值返回，则不会有任何的查询结果返回

SELECT * FROM emp

WHERE comm NOT IN(SELECT comm FROM emp);

(二)ANY操作

对于ANY操作表示的也是子查询中的每一个结果进行比较，而ANY有三种使用方式

>ANY:比子查询之中返回最小的值要大

SELECT * FROM emp

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

<ANY：比子查询之中返回最大的值要小

SELECT * FROM emp

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

=ANY：与IN判断的功能完全相同

SELECT * FROM emp

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

（三）ALL操作

对于ALL也有两种使用方式

>ALL:比子查询之中返回最大的值要大

SELECT * FROM emp

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

<ALL：比子查询之中返回最小的值要小

SELECT * FROM emp

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

至于工作之中使用那个，请根据具体的业务需求来决定

在HAVING中使用子查询

HAVING一定是在GROUP BY之后使用的，表示对分组后的数据进行再次的过滤。如果子查询出现在HAVING

之中，则表示要使用统计函数，而此时的子查询肯定返回的是单行单列数据。

范例：统计出每个部门的编号，平均工资，并且要求这些部门的平均工资高于公司平均工资

第一步：统计出公司的平均工资

SELECT AVG(sal)FROM emp;

第二步：根据需求需要统计查询

SELECT deptno,AVG(sal)

FROM emp

GROUP BY deptno

HAVING AVG(sal)>(SELECT AVG(sal)FROM emp);

如果没有统计，那么就没有HAVING

在SELECT子句中使用子查询（没用）

在SELECT之中使用子查询的情况很少见

范例：查询雇佣编号是7369的姓名，职位，工资，部门名称

SELECT ename,job,sal,

(SELECT d.dname FROM dept d WHERE d.deptno=e.deptno)

FROM emp e

WHERE empno=7369;

这种查询非常可怕，会带来”1+N“此查询问题，例如：现在需求变更，查询出每个雇员的编号，姓名，

职位，部门名称，如果采用以上的代码怎么改昵？

SELECT e.empno,e.ename,e.job,e.sal,

(SELECT d.dname FROM dept d WHERE d.deptno=e.deptno)

FROM emp e;

这个时候查询出全部的雇员需要执行1此查询，而且每一次操作之中，SELECT子句又会查询dept表

，如果emp返回有20行记录，那么就要查询20次的dept表

在FROM子句中使用子查询（核心）

FROM子句主要的功能来确定数据源的，所以如果在FROM子句里面出现子查询，子查询只会返回一种结构：

多行多列（临时表）。

范例：统计出每个部门的编号，名称，位置，部门人数，平均工资

实现方式一：采用多表关联查询统计

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;

实现方式二：利用子查询完成

第一步：查询出每个部门的编号，部门人数，平均工资。只需要emp一张表就可以解决

SELECT deptno,COUNT(empno),AVG(sal)

FROM emp

GROUP BY deptno;

第二步：以上的查询返回的是多行多列数据，这属于一种表的结构，那么可以直接在FROM之后编写，但是

为了方便操作可以为它起一个别名

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(+);

讨论：两种查询操作使用哪一种更好？

为了更好的分析出使用哪种方式好操作，将表中的数据各自扩大100倍，即：emp表1400条记录，而dept表

400条记录，于是进行对比

使用多字段分组数据量：emp表的1400*dept表的400=560000条记录

使用子查询统计

子查询数据量：emp表的1400，最多返回400行记录

子查询与dept表关联：子查询的最多400行*dept表的400行=160000行记录

整体记录：161400条记录

所以通过以上的分析一定要清楚，在日后的开发之中，尽量要回避多表查询，而使用子查询性能会更好

在FROM之中出现子查询还有另外一种功能，指的是整个查询已经无法继续使用统计函数，但是又必须统计

信息的情况下，也可以考虑FROM之后的子查询。