SQL子查询详解-优快云博客

本文链接：https://blog.youkuaiyun.com/dingchenxixi/article/details/72778486

子查询 = 简单查询+限定查询+多表查询+统计查询的综合体

多表查询性能较差，很多多表查询最有利的替代者就是子查询，所以子查询在实际的开发之中使用相当的多
所谓的子查询就是在一个查询之中嵌套了其它若干的查询，子查询语法如下：

SELECT [DISTINCT] * | 列名称 [AS]别名,……..
FROM 表名称1 [别名1] |统计函数,(
     SELECT [DISTINCT] * | 列名称 [AS]别名,……..
     FROM 表名称1 [别名1]
     [WHERE 条件(s)]
     [GROUP BY 分组字段1[,分组字段2,...]]
    [ORDER BY 排序的字段 1,ASC| DESC,排序的字段2 ASC| DESC,….])
 [WHERE 条件(s),
    (SELECT [DISTINCT] * | 列名称 [AS]别名,……..
    FROM 表名称1 [别名1]
    [WHERE 条件(s)]
    [GROUP BY 分组字段1[,分组字段2,...]]
   [ORDER BY 排序的字段 1,ASC| DESC,排序的字段2 ASC| DESC,….])]
[GROUP BY 分组字段1[,分组字段2,...]]
[ORDER BY 排序的字段 1,ASC| DESC,排序的字段2 ASC| DESC,….]

可能在FROM之后出现，也可以在WHERE之后出现，理论上来说子查询可以出现在任意位置上，但从个人而言，子查询出现在WHERE和FROM子句之中比较多
WHERE：子查询一般只返回单行单列、多行单列、单行多列的数据
FROM：子查询返回的一般是多行多列的数据，当作一张临时表出现

范例：要求查询出工资比SMITH高的全部雇员信息
首先需要知道SMITH的工资是多少：

SELECT sal FROM emp WHERE ename='SMITH';

可以看出是单行单列的，所以在WHERE语句后使用子查询：

SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='SMITH');

范例：要求查询出高于公司平均工资的全部雇员信息
首先查询出公司的平均工资：

SELECT AVG(sal) FROM emp;

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

以上返回的是单行单列，但是在子查询之中，也可以返回单行多了的数据

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

SELECT * FROM emp WHERE (job,sal)=(SELECT job,sal FROM emp WHERE ename='ALLEN');

单行多列一般出现很少

如果现在的子查询返回的是多行单列，这个时候就需要使用三种判断符判断了
1、IN操作符：用于指定一个子查询的判断范围
这个操作符的使用实际上与之前讲解的IN是一样的，唯一不同的是，里面的范围由子查询指定

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

但是在使用IN的时候还要注意NOT IN 的问题，如果使用NOT IN 操作，在子查询之中，如果有一个内容是null则不会查询出任何的结果
2、ANY操作符：于每一个内容相匹配，有三种匹配形式
=ANY:功能与IN操作符完全一样

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');

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

3、ALL操作符：与每一个内容相匹配，有两种匹配形式：

ALL：比子查询中返回的最大记录还要大

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

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

以上的所有子查询都是在WHERE子句中出现的，下面观察在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;

这个时候实际上产生了笛卡尔积，一共产生了56条记录
通过子查询完成，所有的统计查询只能在GROUP BY 中出现，所以在子查询之中负责统计数据，而在外部的查询之中，负责将统计数据和dept表数据相统一。

SELECT   d.deptno,d.dname,d.loc, temp.cnt,temp.sal FROM dept d,(SELECT deptno dno,COUNT(empno) cnt,AVG(sal) sal  FROM emp GROUP BY deptno) temp WHERE d.deptno=temp.dno(+);