SQL从入门到出门 第11篇 子查询
本篇介绍SQL:2016（ISO/IEC 9075:2016）标准中定义的子查询（subquery），以及六种主流数据库中的实现及差异：Oracle、MySQL、Microsoft SQL Server、PostgreSQL、Db2、SQLite。
子查询及其分类
在 SQL 中，子查询（subquery）是指嵌套在其他语句（SELECT、INSERT、UPDATE、DELETE、MERGE）中的SELECT语句；另外，子查询中也可以嵌套另外一个子查询。
本篇只关注SELECT语句中的子查询，其他内容在后续关于 DML 语句的篇章中进行介绍。
让我们从一个示例开始，假设我们想要知道哪些员工的薪水大于所有员工薪水的平均值。首先，我们可以查询出所有员工的平均薪水值：
SELECT AVG(salary)
FROM employees;结果约为 6462 。然后，我们查询那些薪水大于该值的员工信息：
SELECT first_name,
last_name,
salary
FROM employees
WHERE salary > 6462
ORDER BY salary;查询结果如下：
以上示例中我们使用了两个查询来解决问题，但是实际问题往往更加复杂；通过使用子查询，可以一次解决问题，同时还能实现更多功能：
SELECT first_name,
last_name,
salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
)
ORDER BY salary;以上查询包含两个SELECT语句，括号内部的查询称为子查询，子查询也称为内查询（inner query）或者嵌套查询（nested query），包含子查询的查询称为外查询（outer query）。
数据库首先运行内查询，获得平均薪水；然后运行外查询，获取薪水大于平均值的员工信息。
子查询必须使用包含在括号（ ( subquery )）中。
在 SQL 标准定义中，子查询分为以下类型：
- 标量子查询（scalar query）：返回单个值（一行一列）的子查询。上面的示例就是一个标量子查询。
- 行子查询（row query）：返回包含一个或者多个值的单行结果（一行多列），标量子查询是行子查询的特例。
- 表子查询（table query）：返回一个虚拟的表（多行多列），行子查询是表子查询的特例。
除了按照返回结果分类之外，子查询还可以分为另外两类：关联子查询（correlated subqueries）和非关联子查询（non-correlated subqueries）。关联子查询会引用外部查询中的列，因而与外部查询产生关联；非关联子查询与于外部查询没有关联。
除此之外，子查询还可以进行嵌套，即在一个子查询中包含另一个子查询。不同的数据库产品支持的子查询嵌套层级各不相同，但已经足够我们使用。
标量子查询
标量子查询可以当作一个标量值使用。
SELECT 子句中的标量子查询
直接看一个示例：
-- scalar query in SELECT clause
SELECT first_name,
last_name,
salary,
salary - (SELECT ROUND(AVG(salary), 0)
FROM employees) AS difference
FROM employees
ORDER BY first_name , last_name;子查询返回一个标量值（平均薪水），其中的 ROUND 函数用于四舍五入，整个查询返回了每个员工的薪水与平均薪水的差值。
WHERE 子句中的标量子查询
在 SQL 语句的WHERE子句中，可以将列值与一个标量子查询的结果进行比较运算，返回满足条件的数据：
SELECT first_name,
last_name,
salary
FROM employees
WHERE salary = (SELECT MAX(salary)
FROM employees)
ORDER BY first_name , last_name;子查询计算出最高的薪水值，外查询通过WHERE子句中的等值比较（=）返回薪水最高的员工信息。除了等号之外，也可以使用其他比较运算符，参考：SQL从入门到出门 第4篇 查询条件。
标量子查询除了用于SELECT和WHERE子句之外，也可以出现在GROUP BY、HAVING以及ORDER BY子句中，使用方法类似。
行子查询
行子查询可以当作一个一行多列的表使用。
SELECT first_name,
last_name,
salary
FROM employees
WHERE (first_name, last_name) = (SELECT MAX(first_name), MAX(last_name)
FROM employees)
ORDER BY first_name , last_name;以上查询中的子查询返回一个一行两列的值，然后在外查询的WHERE条件中查找姓和名都匹配该值的员工信息。查询结果为空，因为没有符合条件的数据。
对于使用行子查询或标量子查询的情况，需要注意子查询结果不能返回多行：
-- Error subquery case
SELECT first_name,
last_name,
salary
FROM employees
WHERE first_name = (SELECT first_name
FROM employees
WHERE job_id = 'IT_PROG')
ORDER BY first_name , last_name;以上查询的问题在于等号（=）只能与一个值进行比较，如果子查询返回多条记录，将会产生错误。
SQLite 允许这种情况，因为它隐式地添加了一个
LIMIT 1，子查询只返回第一行结果。但是，这种情况下的结果不可预测。
实际上，返回多行的子查询就是表子查询。
表子查询
表子查询可以当作一个表使用。
FROM 子句中的表子查询
在 FROM 子句中的子查询，相当于创建了一个临时表，可以作为查询的一个数据源使用：
SELECT column1, column2, ...
FROM (subquery) [AS table_alias];MySQL、SQL Server 和 PostgreSQL 要求 FROM 子句中的子查询必须指定别名。
上例子：
SELECT d.department_name,
ds.sum_salary
FROM departments d
JOIN (SELECT department_id,
SUM(salary) AS sum_salary
FROM employees
GROUP BY department_id) ds
ON (d.department_id = ds.department_id)
ORDER BY d.department_name;上面的子查询位于 JOIN 之后，并且被赋予了一个表名 ds，它计算了各个部门的总薪水。查询最终显示了部门名称和总薪水：
不同数据库产品对于 FROM 子句中的子查询称呼不同。例如，MySQL 称为派生表（derived table），Oracle 称为内联视图（inline view）。
WHERE 子句中的表子查询
在前面的行子查询中，我们知道了如果子查询返回多行记录，不能使用普通的比较运算符（例如等于）进行判断；需要使用其他运算符。
首先，我们可以使用[NOT] IN检查某个值是否包含在列表中：
SELECT first_name,
last_name,
salary
FROM employees
WHERE job_id IN (SELECT job_id
FROM jobs
WHERE max_salary <= 10000)
ORDER BY first_name , last_name;子查询获取了所有最高薪水小于等于 10000 的职位列表，然后外查询使用IN运算符返回属于这些职位的员工信息。如果使用NOT IN运算符，则返回不属于这些职位的员工信息。
另外，我们还可以使用ALL、ANY/SOME运算符比较某个值和列表中的所有值或者任何值：
-- Not For SQLite
SELECT first_name,
last_name,
salary
FROM employees
WHERE salary > ALL (SELECT AVG(salary)
FROM employees
GROUP BY department_id)
ORDER BY first_name , last_name;子查询计算出每个部门的平均薪水，外查询返回薪水大于所有平均薪水的员工，也就是大于平均薪水中的最高值。
ANY/SOME使用方式也一样，不同之处在于它们只需要满足任意比较结果即可。
SQLite 不支持 ALL、ANY/SOME 运算符。
关于子查询，不同数据库产品实现之间的一个差异是：子查询中是否能够使用ORDER BY进行排序。
-- Not For Oracle or SQL Server
SELECT first_name,
last_name,
salary
FROM employees
WHERE job_id IN (SELECT job_id
FROM jobs
WHERE max_salary <= 10000
ORDER BY job_id)
ORDER BY first_name , last_name;子查询在获取职位列表时进行了排序，通常来说这种排序是没有必要的，只有外查询最后的ORDER BY能够决定结果返回的顺序。
Oracle 不支持子查询中的
ORDER BY排序；SQL Server 也不支持子查询中的ORDER BY排序，除非指定了TOP、OFFSET或者FOR XML选项；其他四个数据库支持子查询中的ORDER BY排序。
关联子查询
接下来，我们来看一下更加强大的关联子查询。
关联子查询会引用外部查询中的列，因而与外部查询产生关联。关联子查询对于外查询中的每一行都会运行一次（数据库可能会对此进行优化），而非关联子查询在整个查询运行时只会执行一次。
SELECT job_id,
first_name,
last_name,
salary
FROM employees o
WHERE salary = (SELECT MAX(salary)
FROM employees i
WHERE i.job_id = o.job_id)
ORDER BY job_id, first_name;子查询的WHERE子句中使用了外查询的 job_id 字段作为判断条件，因此外查询先检索出所有的 employees 数据，针对每一行数据，将 job_id 传递给子查询；子查询获取每个职位的最高薪水，然后再根据薪水值对外部的数据行进行过滤；直到外查询中的所有行都被处理完成。
除了WHERE子句外，SELECT列表中也可以使用关联子查询：
SELECT d.department_name,
(SELECT SUM(salary)
FROM employees e
WHERE e.department_id = d.department_id) AS sum_salary
FROM departments d
ORDER BY d.department_name;查询执行的过程与WHERE子句中的关联子查询类似。对于这个示例，也可以使用连接查询完成：
SELECT tmp.department_name,
SUM(tmp.salary) AS sum_salary
FROM (SELECT d.department_name,
e.salary
FROM departments d
LEFT JOIN employees e
ON d.department_id = e.department_id) tmp
GROUP BY tmp.department_name
ORDER BY tmp.department_name;某些数据库产品还支持在FROM子句中使用关联子查询。一般来说，在FROM子句中的两个或多个连接的表之间是互相独立的，无法引用相互之间的列：
-- Error case
SELECT d.department_name,
t.max_salary
FROM departments d
JOIN (SELECT MAX(e.salary) AS max_salary
FROM employees e
WHERE e.department_id = d.department_id) t
ORDER BY d.department_name;以上查询的问题在于JOIN之后的子查询不能引用左侧表（departments）中的列（d.department_id），反过来也不可以。
Oracle、PostgreSQL、Db2 提供了横向子查询（LATERAL subquery），这是一个高级的功能，我们在此只给出一个简单的示例：
-- For Oracle, PostgreSQL and Db2
SELECT d.department_name,
t.max_salary
FROM departments d
CROSS JOIN
LATERAL (SELECT MAX(e.salary) AS max_salary
FROM employees e
WHERE e.department_id = d.department_id) t
ORDER BY d.department_name;使用了LATERAL关键字之后，子查询可以引用左侧表中的列。
Oracle 和 SQL Server 还提供了另外一种功能：CROSS APPLY和OUTER APPLY。CROSS APPLY是一种内连接的变体，OUTER APPLY是一种左外连接的变体，但是它们右侧的子查询都可以引用左侧表中的列：
-- For Oracle and SQL Server
SELECT d.department_name,
t.max_salary
FROM departments d
CROSS APPLY
(SELECT MAX(e.salary) AS max_salary
FROM employees e
WHERE e.department_id = d.department_id) t
ORDER BY d.department_name;查询返回了部门名称和各个部门的最高薪水值。
EXISTS 操作符
EXISTS操作符用于检查子查询的结果的存在性。如果子查询返回任何结果，EXISTS返回 true；否则，返回 false。
SELECT d.department_name
FROM departments d
WHERE EXISTS( SELECT 1
FROM employees e
WHERE e.first_name = 'David'
AND e.department_id = d.department_id)
ORDER BY department_name;以上示例返回哪些部门中存在员工的 first_name 等于“David”的部门名称。EXISTS只关心结果的存在性，因此子查询可以直接指定一个常量（SELECT 1）。
NOT EXISTS执行相反的操作，即子查询不返回任何结果，EXISTS返回 true；否则，返回 false。
我们在前面介绍了[NOT] IN，用于检查某个值是否包含在列表中。需要注意的是[NOT] EXISTS与[NOT] IN的区别：前者只检查存在性，后者需要比较实际的值是否相等（=）。
如果子查询返回空值，NOT EXISTS结果为 true；但是，NOT IN结果为 false，因为NOT (X = NULL)的结果为 NULL。
SELECT d.department_name
FROM departments d
WHERE NOT EXISTS( SELECT 1
FROM employees e
WHERE e.department_id = d.department_id)
ORDER BY department_name;查询返回了没有员工的部门（16条记录）。但是，以下查询没有返回结果：
SELECT d.department_name
FROM departments d
WHERE d.department_id NOT IN ( SELECT e.department_id
FROM employees e)
ORDER BY department_name;这是因为有一个员工（employee_id = 178）没有分配部门，子查询的结果中包含 NULL 值。这个问题可以通过使用函数将空值转换为特殊值来解决：
SELECT d.department_name
FROM departments d
WHERE d.department_id NOT IN ( SELECT COALESCE(e.department_id, 0)
FROM employees e)
ORDER BY department_name;通常来说，
[NOT] EXISTS的性能比[NOT] IN好一些，尽量使用[NOT] EXISTS。
| 数据库 | 子查询 | 描述 |
|---|---|---|
| Oracle | OK | Oracle 不支持子查询中的ORDER BY排序 Oracle 提供了横向子查询（LATERAL subquery） Oracle 提供了 CROSS APPLY和OUTER APPLY
|
| MySQL | OK | MySQL 要求FROM子句中的子查询需要指定别名 MySQL 支持子查询中的 ORDER BY排序 |
| SQL Server | OK | SQL Server 要求FROM子句中的子查询需要指定别名 SQL Server 不支持子查询中的 ORDER BY排序，除非指定了TOP、OFFSET或者FOR XML选项 SQL Server 提供了 CROSS APPLY和OUTER APPLY
|
| PostgreSQL | OK | PostgreSQL 要求FROM子句中的子查询需要指定别名 PostgreSQL 支持子查询中的 ORDER BY排序 PostgreSQL 提供了横向子查询（LATERAL subquery） |
| Db2 | OK | Db2 支持子查询中的ORDER BY排序 Db2 提供了横向子查询（LATERAL subquery） |
| SQLite | OK | SQLite 支持子查询中的ORDER BY排序 SQLite 为子查询表达式隐式地添加了一个 LIMIT 1，子查询只返回第一行结果 |