第 19 篇子查询

imodouAbout 10 minMySQL

《MySQL 入门教程》第 19 篇子查询

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;

第 19 篇 子查询

第 19 篇子查询