什么是子查询？
子查询是在一个完整的查询语句中，嵌套不同功能的小查询，从而完成复杂查询的一种编写形式
子查询是指嵌套在其他语句（select、insert、update、delete 等）中的 select 语句；子查询也称为内查询（inner query）或者嵌套查询（nested query）；子查询必须位于括号之中。
子查询都可以出现在哪里呢？
select
...(select)
from
...(select)
where
...(select)
1.非关联子查询
在SELECT子句、GROUP BY子句、HAVING子句、ORDER BY子句中均可使用子查询语句，较常用的是WHERE子句、HAVING子句和FROM子句。
1.1执行逻辑
在非关联子查询中，子查询语句可以独立执行，查询结果是一个数据，一条数据，或者一张临时数据表，主查询可使用子查询结果进行操作。
1.2在WHERE子句中使用子查询
在WHERE子句中，通常使用子查询结果作为筛选条件。
SQL 中的子查询可以分为以下三种类型：
标量子查询（Scalar Subquery）：返回单个值（一行一列）的子查询。上面的示例就是一个标量子查询。
行子查询（Row Subquery）：返回单行结果（一行多列）的子查询，标量子查询是行子查询的一个特例。
表子查询（Table Subquery）：返回一个虚拟表（多行多列）的子查询，行子查询是表子查询的一个特例。
标量子查询
标量子查询的结果就像一个常量一样，可以用于 select、where、group by、having 以及 ORDER BY 等子句中。以下示例在 select 列表中使用标量子查询计算员工的月薪与平均月薪的差值：
select emp_name, salary, salary - (select avg(salary) from employee) as salary_diff from employee where emp_id <= 6;
行子查询
行子查询可以当作一个一行多列的临时表使用。以下语句查找所有与“孙乾”在同一个部门并且职位相同的员工：
表子查询
当子查询返回的结果包含多行数据时，称为表子查询。表子查询通常用于查询条件或者 FROM 子句中。
查询条件中的子查询
对于 WHERE 中的子查询，需要注意外部查询的条件中不能使用比较运算符。以下是一个错误的示例：
-- 错误示例
SELECT emp_name FROM employee WHERE job_id = (SELECT job_id FROM employee WHERE dept_id = 3);
该语句执行时将会返回一个错误信息：单行子查询返回了多行数据。因为财务部（编号为 3）中包含多个不同的职位，单个值（外部查询条件中的 job_id）与多个值（子查询结果中的多个 job_id 值）不能使用比较运算符（=、!=、<、<=、>、>=）进行判断。
对于这种可能返回多行数据的表子查询，可以使用 IN 和 NOT IN 运算符进行判断。以上示例可以使用 IN 运算符改写如下：
SELECT emp_name FROM employee WHERE job_id IN (SELECT job_id FROM employee WHERE dept_id = 3);
注意：IN，或者NOT IN 后的数据范围内存在值为NULL的数据，则查询结果为空。
IN 运算符用于判断查询条件中的字段取值是否位于子查询返回的列表之中。该语句实际上是返回了财务部门所有的员工。
除了 IN 运算符之外，ALL、ANY/SOME 运算符与比较运算符的结合也可以用于判断子查询的结果。
ALL、ANY/SOME 运算符
ALL 运算符与比较运算符（=、!=、<、<=、>、>=）结合表示等于、不等于、小于、小于等于、大于或者大于等于子查询结果中的所有值。以下示例查找入职日期晚于研发部所有员工的员工信息：
SELECT emp_name, hire_date FROM employee WHERE hire_date > ALL (SELECT e.hire_date FROM employee e JOIN department d ON (d.dept_id = e.dept_id) WHERE d.dept_name = '研发部');
“> ALL”表示比结果中的所有值都大，也就是大于结果中的最大值。
注意：
<>ALL：相当于NOT IN
>ALL：大于其后数据范围内的最大值
<ALL：小于其后数据范围内的最小值
=ALL：🙅错误用法
ANY/SOME 运算符与比较运算符（=、!=、<、<=、>、>=）结合表示等于、不等于、小于、小于等于、大于或者大于等于子查询结果中的任意值。上一节中的 IN 运算符示例可以使用 ANY/SOME 运算符改写如下：
SELECT emp_name FROM employee WHERE job_id = ANY (SELECT job_id FROM employee WHERE dept_id = 3);
注意：
>ANY：大于其后数据范围内最小值
<ANY：小于其后数据范围内最大值
=ANY：效果相当于IN
<>ANY：🙅错误用法
from 中的子查询
在 from 中的子查询相当于一个临时表。以下语句查找各个部门的名称和平均月薪：
SELECT d.dept_name AS "部门名称",ds.avg_salary AS "平均月薪"
FROM department d
LEFT JOIN (SELECT dept_id,AVG(salary) AS avg_salary FROM employee GROUP BY dept_id) ds
ON (d.dept_id = ds.dept_id);
其中，JOIN 后面的子查询创建了一个临时表（表名为 ds），它包含了各个部门的编号和平均月薪；然后将表 department 与 ds 进行左外连接查询。该查询最终返回了每个部门的名称和平均月薪：
在HAVING子句中使用子查询
在HAVING子句中使用子查询，即对分组进行过滤，子查询往往返回的都是一个具体的数据（单行单列）。
题目：筛选出平均工资大于全体员工平均工资的部门
SELECT deptno, avg(sal) FROM table1 GROUP BY deptno HAVING avg(sal) > (SELECT avg(sal) FROM table1);
