SQL 子查询

SQL 连接查询，我们介绍了如何在 SQL 语句中使用连接查询（JOIN）获取多个表中的关联数据，具体讨论了内连接、左/右/全外连接、交叉连接、自然连接以及自连接的原理和使用方法。

除了连接查询，SQL 还提供了另一种同时查询多个表的方法：子查询（Subquery）。本文我们就来了解一下各种类型的子查询和相关的运算符。

什么是子查询

我们先来考虑一个问题，哪些员工的月薪大于所有员工的平均月薪？可以先使用 AVG 函数获取所有员工的平均月薪：

SELECT AVG(salary)
  FROM employee;

AVG(salary)|
-----------|
9832.000000|

然后将该查询的结果作为下面语句的查询条件，返回月薪大于 9832 的员工：

SELECT emp_name, salary
  FROM employee
 WHERE salary > 9832;

该语句的结果如下：

我们使用了两个查询来解决这个简单的问题，然而实际应用中的需求往往更加复杂；显然我们需要更加高级的查询功能。

SQL 提供了一种查询方式叫做子查询，可以非常容易地解决这个问题：

SELECT emp_name, salary
  FROM employee
 WHERE salary > (
                 SELECT AVG(salary)
                   FROM employee
                );

该示例中包含两个查询语句（SELECT）；括号内部的查询称为子查询，用于获得员工的平均月薪；包含子查询的查询称为外部查询，用于返回月薪大于平均月薪的员工的信息。该查询的最终结果与上面的示例相同。

简单来说，子查询是指嵌套在其他语句（SELECT、INSERT、UPDATE、DELETE 等）中的 SELECT 语句；子查询也称为内查询（inner query）或者嵌套查询（nested query）；子查询必须位于括号之中。

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;

该语句执行的结果如下：

salary_diff 的结果代表了员工月薪与平均月薪的差距。

行子查询

行子查询可以当作一个一行多列的临时表使用。以下语句查找所有与“孙乾”在同一个部门并且职位相同的员工：

-- Oracle、MySQL 以及 PostgreSQL 实现
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 != '孙乾';

子查询返回了“孙乾”所在的部门编号和职位编号，这两个值构成了一行数据；然后外部查询的 WHERE 条件使用该数据进行过滤，AND 操作符用于排除“孙乾”自己。该语句执行的结果如下：

行子查询的实际使用较少，SQL Server 中不支持行子查询。

表子查询

当子查询返回的结果包含多行数据时，称为表子查询。表子查询通常用于查询条件或者 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);

emp_name|
--------|
孙尚香   |
孙丫鬟   |

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”表示比结果中的所有值都大，也就是大于结果中的最大值。该查询的结果如下：

研发部最晚入职的是“马岱”，入职日期为 2014 年 9 月 16 日；所以该查询返回的是这个日期之后入职的员工。

ANY/SOME 运算符与比较运算符（=、!=、<、<=、>、>=）结合表示等于、不等于、小于、小于等于、大于或者大于等于子查询结果中的任意值。上一节中的 IN 运算符示例可以使用 ANY/SOME 运算符改写如下：

SELECT emp_name
  FROM employee
 WHERE job_id = ANY (SELECT job_id FROM employee WHERE dept_id = 3);

该查询同样返回了财务部门所有的员工。

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 进行左外连接查询。该查询最终返回了每个部门的名称和平均月薪：

左外连接确保了不会丢失“保卫部”的信息，即使它目前还没有任何员工。

各种数据库对于 FROM 中的子查询叫法不同。例如，MySQL 中称为派生表（derived table），Oracle 中称为内联视图（inline view）。

除了按照返回的结果分类之外，子查询还可以按照另一种方式进行分类：关联子查询（Correlated Subquery）和非关联子查询（Non-correlated Subquery）。

子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件进行执行，那么这样的子查询叫做非关联子查询。

同样，如果子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的执行方式就称为关联子查询。

单说概念有点抽象，我们用数据表举例说明一下。

文件中一共包括了5张表，player表为球员表，team为球队表，team_score为球队比赛表，player_score为球员比赛成绩表，height_grades为球员身高对应的等级表。

其中player表，也就是球员表，一共有37个球员，如下所示：

team表为球队表，一共有3支球队，如下所示：

team_score表为球队比赛成绩表，一共记录了两场比赛的成绩，如下所示：

player_score表为球员比赛成绩表，记录了一场比赛中球员的表现。这张表一共包括19个字段，代表的含义如下：

其中shoot_attempts代表总出手的次数，它等于二分球出手和三分球出手次数的总和。比如2019年4月1日，韦恩·艾灵顿在底特律活塞和印第安纳步行者的比赛中，总出手次数为19，总命中10，三分球13投4中，罚球4罚2中，因此总分score=(10-4)×2+4×3+2=26，也就是二分球得分12+三分球得分12+罚球得分2=26。

需要说明的是，通常在工作中，数据表的字段比较多，一开始创建的时候会知道每个字段的定义，过了一段时间再回过头来看，对当初的定义就不那么确定了，容易混淆字段，解决这一问题最好的方式就是做个说明文档，用实例举例。

比如shoot_attempts是总出手次数（这里的总出手次数=二分球出手次数+三分球出手次数，不包括罚球的次数），用上面提到的韦恩·艾灵顿的例子做补充说明，再回过头来看这张表的时候，就可以很容易理解每个字段的定义了。

我们以NBA球员数据表为例，假设我们想要知道哪个球员的身高最高，最高身高是多少，就可以采用子查询的方式：

SELECT player_name, height FROM player WHERE height = (SELECT max(height) FROM player)

运行结果：（1条记录）

你能看到，通过SELECT max(height) FROM player可以得到最高身高这个数值，结果为2.16，然后我们再通过player这个表，看谁具有这个身高，再进行输出，这样的子查询就是非关联子查询。

如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为关联子查询。比如我们想要查找每个球队中大于平均身高的球员有哪些，并显示他们的球员姓名、身高以及所在球队ID。

首先我们需要统计球队的平均身高，即SELECT avg(height) FROM player AS b WHERE a.team_id = b.team_id，然后筛选身高大于这个数值的球员姓名、身高和球队ID，即：

SELECT player_name, height, team_id FROM player AS a WHERE height > (SELECT avg(height) FROM player AS b WHERE a.team_id = b.team_id)

运行结果：（18条记录）