「这是我参与11月更文挑战的第12天，活动详情查看：2021最后一次更文挑战」。
19.1 子查询概述
子查询（Subquery）是指嵌套在其他 SQL 语句（ SELECT、INSERT、UPDATE、DELETE 等）中的查询语句。子查询也称为内查询（inner query），必须位于括号之中；包含子查询的查询也称为外查询（outer query）。子查询支持多层嵌套，也就是子查询中包含其他子查询。
例如，以下语句返回了月薪大于平均月薪的员工：
select emp_name, salary
from employee
where salary > (
select avg(salary)
from employee
);
其中，括号内部的子查询用于获得员工的平均月薪（9832.00）；外查询用于返回月薪大于平均月薪的员工信息。该查询的结果如下：
emp_name |salary |
----------|--------|
刘备 |30000.00|
关羽 |26000.00|
张飞 |24000.00|
诸葛亮 |24000.00|
孙尚香 |12000.00|
赵云 |15000.00|
法正 |10000.00|
MySQL 中的子查询可以分为以下三种类型：
- 标量子查询（Scalar Subquery）：返回单个值（一行一列）的子查询。上面的示例就是一个标量子查询。
- 行子查询（Row Subquery）：返回单行结果（一行多列）的子查询，标量子查询是行子查询的一个特例。
- 表子查询（Table Subquery）：返回一个虚拟表（多行多列）的子查询，行子查询是表子查询的一个特例。
19.2 标量子查询
标量子查询的结果就像一个常量一样，可以用于 SELECT、WHERE、GROUP BY、HAVING 以及 ORDER BY 等子句中。对于上面的子查询示例，实际相当于先执行以下语句得到平均月薪：
select avg(salary)
from employee;
avg(salary)|
-----------|
9832.000000|
然后将该值替换到外查询中：
select emp_name, salary
from employee
where salary > ( 9832 );
19.3 行子查询
行子查询可以当作一个一行多列的临时表使用。以下语句查找所有与“黄忠”在同一个部门并且职位相同的员工：
select emp_name, dept_id, job_id
from employee
where (dept_id, job_id) = (select dept_id, job_id
from employee
where emp_name = '黄忠')
and emp_name != '黄忠';
emp_name|dept_id|job_id|
--------|-------|------|
魏延 | 2| 4|
子查询返回了“黄忠”所在的部门编号和职位编号，这两个数值构成了一行数据；外部查询的 WHERE 条件使用该数据行进行过滤，AND 操作符用于排除“黄忠”自己。
行子查询可以使用以下比较运算符：=、>、<、>=、<=、<>、!=、<=>。如果行子查询产生多行记录将会返回错误，因为这些运算符只能和单个记录进行比较。
19.4 表子查询
当子查询返回的结果包含多行数据时，称为表子查询。表子查询通常用于 FROM 子句或者查询条件中。
19.4.1 派生表
当子查询出现在 FROM 子句中时，相当于创建了一个语句级别的临时表或者视图，也被称为派生表（derived table）。例如：
select d.dept_name as "部门名称",
coalesce(de.emp_number,0) as "员工数量"
from department d
left join (select dept_id,
count(*) as emp_number
from employee
group by dept_id) de
on (d.dept_id = de.dept_id);
部门名称 |员工数量|
--------|-------|
行政管理部| 3|
人力资源部| 3|
财务部 | 2|
研发部 | 9|
销售部 | 8|
保卫部 | 0|
其中，left join 后面是一个派生表（必须指定别名，这里是 de），它包含了各个部门的编号和员工数量；然后将 department 与 de 进行左外连接查询，返回了部门信息和对应的员工数量。
19.4.2 IN 操作符
当 WHERE 条件中的子查询返回多行数据时，不能再使用普通的比较运算符，因为它们不支持单个值和多个值的比较；如果想要判断某个字段是否在子查询返回的数据列表中，可以使用 IN 操作符。例如：
select emp_name
from employee
where job_id in (select job_id from job);
子查询返回了所有的职位编号，in 操作符用于返回 job_id 等于其中任何一个编号的员工，因此结果会返回所有的员工。该语句等价于以下语句：
select emp_name
from employee
where job_id = 1
or job_id = 2
...
or job_id = 10;
NOT IN 操作符执行和 IN 相反的操作，也就是当表达式不等于任何子查询返回结果时为 True。
19.4.3 ALL、ANY/SOME 操作符
除了 IN 运算符之外，ALL、ANY/SOME 运算符与比较运算符的结合也可以用于判断子查询的返回结果：
operand comparison_operator ALL (subquery)
operand comparison_operator ANY (subquery)
operand comparison_operator SOME (subquery)
其中，comparison_operator 是比较运算符，包括 =、>、<、>=、<=、<>、!=。
ALL 和比较运算符一起使用表示将表达式和子查询的结果进行比较，如果比较的结果都为 True 时最终结果就为 True。例如：
select emp_name, salary
from employee
where salary > all (select e.salary
from employee e
join department d on (d.dept_id = e.dept_id)
where d.dept_name = '研发部');
emp_name|salary |
--------|--------|
刘备 |30000.00|
关羽 |26000.00|
张飞 |24000.00|
诸葛亮 |25000.00|
其中，子查询返回了研发部所有员工的月薪；“> all”表示大于子查询结果中的所有值，也就是大于子查询结果中的最大值（15000）。
对于 ALL 操作符，有两个需要注意的情况，就是子查询结果为空或者存在 NULL 值。例如：
select emp_name, salary
from employee
where salary > all (select 999999 from dual where 1=0);
以上查询会返回所有的员工，因为子查询返回结果为空集，外查询相当于没有 where 条件。
以下查询不会返回任何结果：
select emp_name, salary
from employee
where salary > all (select max(999999) from dual where 1=0);
由于子查询返回一行数据 NULL，任何数值和 NULL 比较的结果都是未知（unknown ），所以外查询返回空集。
ANY/SOME 和比较运算符一起使用表示将表达式和子查询的结果进行比较，如果任何比较的结果为 True，最终结果就为 True。例如：
select emp_name
from employee
where job_id = any (select job_id from job);
该语句等价于上面的 IN 操作符示例，也就是说 = ANY 和 IN 操作符等价。
另外，需要注意的是 NOT IN 等价于 <> ALL，而不是 <> ANY。因为“a not in (1,2,3)”和“a <> all (1,2,3)”等价于：
a <> 1 and a <> 2 and a <>3
“a <> any (1,2,3)”等价于：
a <> 1 or a <> 2 or a <>3
19.5 关联子查询
在上面的示例中，子查询和外查询之间没有联系，可以单独运行。这种子查询也称为非关联子查询（Non-correlated Subquery）。另一类子查询会引用外查询中的字段，从而与外部查询产生关联，也称为关联子查询（Correlated Subquery）。
以下示例通过使用关联子查询获得各个部门的员工数量：
select d.dept_name as "部门名称",
(select count(*)
from employee
where dept_id = d.dept_id) as "员工数量"
from department d;
部门名称 |员工数量|
--------|-------|
行政管理部| 3|
人力资源部| 3|
财务部 | 2|
研发部 | 9|
销售部 | 8|
保卫部 | 0|
其中，子查询的 where 条件中使用了外查询的部门编号（d.dept_id），从而与外查询产生关联。该语句执行时，外查询先检索出所有的部门数据，针对每条记录再将 d.dept_id 传递给子查询；子查询返回每个部门的员工数量。该查询的结果与 19.4.1 中的派生表示例相同。
19.6 EXISTS 操作符
EXISTS 操作符用于判断子查询结果的存在性。如果子查询存在任何结果，EXISTS 返回 True；否则，返回 False。
例如，以下语句返回了存在女性员工的部门：
select d.dept_name
from department d
where exists ( select 1
from employee e
where e.sex = '女'
and e.dept_id = d.dept_id
);
dept_name|
---------|
财务部 |
研发部 |
其中，exists 之后是一个关联子查询，先执行外查询找到 d.dept_id；然后依次将 d.dept_id 传递给子查询，判断该部门是否存在女性员工，如果存在则返回部门信息。
📝EXISTS 只判断结果的存在性，因此子查询的 SELECT 列表中的内容无所谓，通常使用一个常量值。EXISTS 只要找到任何数据，立即终止子查询的执行，因此可以提高查询的性能。
NOT EXISTS 执行相反的操作。如果想要查找不存在女性员工的部门，可以将上例中的 EXISTS 替换成 NOT EXISTS。
[NOT] EXISTS 和 [NOT] IN 都可以用于判断子查询返回的结果，但是它们之间存在一个重要的区别：[NOT] EXISTS 只检查存在性，[NOT] IN 需要比较实际的值是否相等。因此，当子查询的结果包含 NULL 值时，EXISTS 仍然返回结果，NOT EXISTS 不返回结果；但是此时 IN 和 NOT IN 都不会返回结果，因为 (X = NULL) 和 NOT (X = NULL) 的结果都是未知。
以下示例演示了这两者之间的区别：
select d.dept_name
from department d
where not exists ( select null
from employee e
where e.dept_id = d.dept_id
);
dept_name|
---------|
保卫部 |
select d.dept_name
from department d
where d.dept_id not in ( select null
from employee e
);
dept_name|
---------|
第一个查询使用了 NOT EXISTS，子查询中除了“保卫部”之外的部门都有返回结果（NULL 也是结果），所以外查询只返回“保卫部”。第二个查询使用了 NOT IN，子查询中返回的都是 NULL 值；d.dept_id = NULL 的结果是未知，加上 NOT 之后仍然未知，所以查询没有返回任何结果。
📝EXISTS 和 IN 操作符返回左表（外查询）中与右表（子查询）至少匹配一次的数据行，实际上是一种半连接（Semi-join）；NOT EXISTS 或者 NOT IN 操作符返回左表（外查询）中与右表（子查询）不匹配的数据行，实际上是一种反连接（Anti-join）。
19.7 横向派生表
对于派生表而言，它必须能够单独运行，而不能依赖其他表。例如，以下语句想要返回每个部门内月薪最高的员工：
select d.dept_name, t.emp_name, t.salary
from department d
left join (select e.dept_id, e.emp_name, e.salary
from employee e
where e.dept_id = d.dept_id
order by e.salary desc
limit 1
) t on d.dept_id = t.dept_id;
RROR 1054 (42S22): Unknown column 'd.dept_id' in 'where clause'
该语句失败的原因在于子查询 t 不能引用外查询中的 department 表。
从 MySQL 8.0.14 开始，派生表支持 LATERAL 关键字前缀，表示允许派生表引用它所在的 FROM 子句中的其他表。这种派生表被称为横向派生表（Lateral Derived Table）。
对于上面的问题，可以使用 LATERAL 派生表实现：
select d.dept_name, t.emp_name, t.salary
from department d
left join lateral (select e.dept_id, e.emp_name, e.salary
from employee e
where e.dept_id = d.dept_id
order by e.salary desc
limit 1
) t on d.dept_id = t.dept_id;
dept_name |emp_name|salary |
------------|--------|--------|
行政管理部 |刘备 |30000.00|
人力资源部 |诸葛亮 |25000.00|
财务部 |孙尚香 |12000.00|
研发部 |赵云 |15000.00|
销售部 |法正 |10000.00|
保卫部 | | |
该语句在 left join 之后加上了一个 lateral 关键字，使得子查询 t 能够引用前面的 department 表中的字段。
📝关于 MySQL 横向派生表的详细介绍和使用案例，可以参考这篇文章。
如果你使用的是 MySQL 5.7 以及之前的版本，可以利用 MySQL 中的自定义变量实现相同的效果：
select d.dept_name, w.emp_name, w.salary
from department d
left join (
select *
from (
select a.*, if(@did = a.dept_id, @rn := @rn+1, @rn := 1) as rn, @did := a.dept_id as did
from (select * from employee e order by dept_id, salary desc) a
cross join (select @rn := 0 rn, @did := 0) b
) as t
where t.rn <= 1
) as w on d.dept_id = w.dept_id;
