SQL 支持查询语句的嵌套，也就是在一个语句中包含其他的查询语句。嵌套子查询可以用于实现灵活复杂的查询语句。

本文比较五种主流数据库实现的子查询功能，包括 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite。

查询中的查询

假如我们想要知道哪些员工的月薪大于或等于所有员工的平均月薪，应该如何编写查询语句？显然，我们首先需要得到所有员工的平均月薪，这个可以使用 AVG 聚合函数实现：

SELECT AVG(salary)
FROM employee;

AVG(salary)
-----------
9832.00

然后我们可以将该结果作为以下查询的过滤条件，返回月薪大于或等于 9832 元的员工：

SELECT emp_name AS "员工姓名", salary AS "月薪"
FROM employee
WHERE salary > 9832;

查询返回的结果如下：

员工姓名|月薪 
-------|--------
刘备   |30000.00
关羽   |26000.00
张飞   |24000.00
诸葛亮 |24000.00
孙尚香 |12000.00
赵云   |15000.00
法正   |10000.00

为了解决以上问题，我们使用了两个查询语句。那么，能不能在一个查询语句中直接返回最终的结果呢？

SQL 提供了一种被称为嵌套子查询的功能，可以帮助我们解决这个问题。例如：

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

以上示例中包含两个 SELECT 语句，括号内的 SELECT 语句被称为子查询（Subquery），它的作用是返回员工的平均月薪。包含子查询的 SELECT 语句被称为外部查询（Outer Query），它的作用是返回月薪大于平均月薪的员工。

ℹ️根据定义，子查询就是指嵌套在其他语句（包括 SELECT、INSERT、UPDATE、DELETE 等）中的 SELECT 语句。子查询也被称为内查询（Inner Query）或者嵌套查询（Nested Query），子查询必须位于括号之中。

SQL 语句中的子查询可以按照返回结果分为以下三种类型：

• 标量子查询（Scalar Subquery），返回单个值（一行一列）的子查询。上面的示例就是一个标量子查询。

• 行子查询（Row Subquery），返回单个记录（一行多列）的子查询。标量子查询是行子查询的一个特例。

• 表子查询（Table Subquery），返回一个临时表（多行多列）的子查询。行子查询是表子查询的一个特例。

下面我们分别介绍这三种子查询的作用。

标量子查询

标量子查询返回单个结果值，可以像常量一样被用于 SELECT、WHERE、GROUP BY、HAVING 以及 ORDER BY 等子句中。

例如，以下查询用于计算员工月薪与平均月薪之间的差值：

SELECT emp_name AS "员工姓名", salary AS "月薪",
       salary - (SELECT AVG(salary) FROM employee) AS "差值"
FROM employee;

其中，外部查询的 SELECT 列表中包含一个标量子查询，返回了员工的平均月薪。最终查询返回的结果如下：

员工姓名|月薪     |差值 
-------|--------|------------
刘备   |30000.00|20168.000000
关羽   |26000.00|16168.000000
张飞   |24000.00|14168.000000
...

行子查询

行子查询返回一个一行多列的记录，一般较少使用。例如，以下语句用于查找所有与“孙乾”在同一个部门并且职位相同的员工：

-- Oracle、MySQL、PostgreSQL 以及 SQLite
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 运算符用于排除“孙乾”自己。查询返回的结果如下：

emp_name|dept_id|job_id
--------|-------|------
庞统    |       5| 10
蒋琬    |       5| 10
黄权    |       5| 10
...

⚠️Microsoft SQL Server 目前不支持行子查询。

表子查询

表子查询返回一个多行数据的结果集，通常被用于 WHERE 条件或者 FROM 子句中。

WHERE 条件中的子查询

对于 WHERE 子句中的子查询，外部查询的查询条件中不能使用比较运算符。例如：

SELECT emp_name
FROM employee
WHERE job_id = (SELECT job_id 
FROM employee 
WHERE dept_id = 1);

执行以上语句，将会返回类似这样的错误信息：子查询返回了多行数据。这是因为单个值（外部查询条件中的 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 = 1);

IN 运算符可以用于判断字段的值是否属于某个列表。我们在以上语句中通过子查询生成了一个列表，查询返回的结果如下：

emp_name
--------
刘备 
关羽 
张飞 

该查询返回了“行政管理部”的全体员工。

ALL、ANY 运算符

ALL 运算符与比较运算符（=、!=、<、<=、>、>=）的组合分别表示等于、不等于、小于、小于或等于、大于、大于或等于子查询结果中的全部数据。例如，以下语句查找比“研发部”全体员工更晚入职的员工信息：

-- Oracle、MySQL、Microsoft SQL Server 以及 PostgreSQL
SELECT emp_name AS "员工姓名", hire_date AS "入职日期"
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 运算符返回了比“研发部”全体员工更晚入职的员工。查询返回的结果如下：

员工姓名|入职日期 
-------|----------
法正   |2017-04-09
庞统   |2017-06-06
蒋琬   |2018-01-28
...

“研发部”中最晚入职的是“马岱”，入职日期为 2014 年 9 月 16 日。因此，以上查询返回了在这个日期之后入职的员工。

⚠️SQLite 目前不支持 ALL 运算符。

如果我们将以上查询中的 >ALL 替换为 =ALL 运算符：

-- Oracle、MySQL、Microsoft SQL Server 以及 PostgreSQL
SELECT emp_name AS "员工姓名", hire_date AS "入职日期"
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 运算符相当于多个 AND 运算符的组合，IN 运算符相当于多个 OR 运算符的组合。

对于 Oracle 而言，ALL 运算符也可以直接与一个常量列表进行比较。例如：

-- Oracle
SELECT emp_name AS "员工姓名", salary AS "入职日期"
FROM employee
WHERE salary >ALL (10000, 15000, 20000);

该查询返回了月薪大于 20000 元的员工信息。

除 ALL 运算符外，ANY 运算符与比较运算符（=、!=、<、<=、>、>=）的组合分别表示等于、不等于、小于、小于或等于、大于、大于或等于子查询结果中的任何数据。

我们可以将前面的 IN 运算符示例使用 ANY 运算符改写如下：

-- Oracle、MySQL、Microsoft SQL Server 以及 PostgreSQL
SELECT emp_name
FROM employee
WHERE job_id =ANY (SELECT job_id 
FROM employee
WHERE dept_id = 1);

查询条件中的=ANY 运算符表示等于子查询中的任何结果。该查询同样返回了“行政管理部”的全体员工。

⚠️SQLite 目前不支持 ANY 运算符。另外，在其他数据库中我们也可以使用 SOME 关键字替代 ANY。

如果我们将以上查询中的 =ANY 替换为 !=ANY 运算符：

-- Oracle、MySQL、Microsoft SQL Server 以及 PostgreSQL
SELECT emp_name
FROM employee
WHERE job_id !=ANY (SELECT job_id 
FROM employee 
WHERE dept_id = 1);

查询返回的结果如下：

emp_name
--------
刘备 
关羽 
张飞 
... 

该查询返回了所有的员工。这是因为 ANY 运算符相当于多个 OR 运算符的组合，任何员工的 job_id 都至少不等于子查询返回的某个 job_id。

对于 Oracle 而言，ANY 运算符也可以直接与一个常量列表进行比较。例如：

-- Oracle
SELECT emp_name
FROM employee
WHERE job_id !=ANY (1, 2, 2); -- 等价于 job_id != 1 OR job_id != 2

“行政管理部”3 名员工的 job_id 分别为 1、2、2，因此该查询同样返回了所有的员工。

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 进行了左外连接。最终查询返回的结果如下：

部门名称 |平均月薪 
--------|------------
行政管理部|26666.666667
人力资源部|13166.666667
财务部   | 9000.000000
研发部   | 7577.777778
销售部   | 5012.500000
保卫部   | 

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

ℹ️不同数据库对于 FROM 子句中的子查询称呼不同。例如，MySQL 称之为派生表（Derived Table），Oracle 则称之为内联视图（Inline View）。

一般来说，子查询可以像普通查询一样包含各种子句，例如 JOIN、WHERE、GROUP BY 等，甚至可以嵌套其他的子查询。但是需要注意，不同数据库对于子查询中 ORDER BY 子句的处理方式存在差异。例如：

-- MySQL、PostgreSQL 以及 SQLite
SELECT emp_name, salary
FROM employee
WHERE job_id IN (SELECT job_id
FROM job
WHERE max_salary >= 20000
ORDER BY job_id)
ORDER BY emp_name;

其中，子查询返回了最高月薪大于或等于 20000 元的职位，同时使用了 ORDER BY 进行数据的排序。对于 MySQL、PostgreSQL 以及 SQLite，以上语句可以正常返回查询结果，Oracle和 Microsoft SQL Server 则会返回语法错误。

通常来说，子查询中的排序没有实际意义，不会影响到查询结果和显示顺序。只有外部查询中的 ORDER BY 子句能够决定最终结果的显示顺序。

关联子查询

我们在前面介绍的子查询与外部查询之间没有任何关联，可以单独运行并返回结果，它们都属于非关联子查询（Non-correlated Subquery）。此外，SQL 中还有一类子查询，它们在执行时需要使用外部查询中的字段值，从而和外部查询产生关联。因此，这类子查询也被称为关联子查询（Correlated Subquery）。

例如，以下语句通过一个关联子查询返回了每个部门的平均月薪：

SELECT d.dept_name AS "部门名称",
(SELECT AVG(salary) AS avg_salary
FROM employee
WHERE dept_id = d.dept_id) AS "平均月薪"
FROM department d
ORDER BY d.dept_id;

其中，子查询的 WHERE 条件中使用了外部查询的部门编号字段（d.dept_id），从而与外部查询产生关联。我们在执行该查询时，首先通过外部查询找出所有的部门数据，然后依次将 d.dept_id 传递给子查询，执行子查询，返回每个部门的平均月薪。

ℹ️对于外部查询返回的每条记录，关联子查询都会执行一次（数据库可能会对此进行优化），非关联子查询只会独立执行一次。

另外，WHERE 子句中也可以使用关联子查询。例如，以下查询返回了每个部门中最早入职的员工：

SELECT d.dept_name AS "部门名称", 
 o.emp_name AS "员工姓名", 
 o.hire_date AS "入职日期"
FROM employee o
JOIN department d ON (d.dept_id = o.dept_id)
WHERE o.hire_date = (SELECT MIN(i.hire_date)
FROM employee i
WHERE i.dept_id = o.dept_id);

我们执行该语句时，数据库首先找到外部查询中的每个员工，依次将 o.dept_id 字段传递给子查询，子查询返回当前部门中第一个员工的入职日期，然后将该日期传递给外部查询进行判断。查询返回的结果如下：

部门名称 |员工姓名|入职日期 
--------|-------|----------
行政管理部|刘备   |2000-01-01
行政管理部|关羽   |2000-01-01
行政管理部|张飞   |2000-01-01
人力资源部|诸葛亮 |2006-03-15
财务部   |孙尚香 |2002-08-08
财务部   |孙丫鬟 |2002-08-08
研发部   |赵云   |2005-12-19
销售部   |法正   |2017-04-09

“行政管理部”返回了多名员工，因为他们在同一天入职。

横向子查询

关联子查询可以使用外部查询中的字段，但不能使用同一级别的其他查询或者表中的字段。以下是一个错误的示例：

SELECT d.dept_name, t.max_salary
FROM department d
JOIN (SELECT MAX(e.salary) AS max_salary
FROM employee e
WHERE e.dept_id = d.dept_id) t;

其中，JOIN 子句中的子查询使用了 department 表中的字段。执行该查询将会返回一个错误信息。因为子查询内部无法使用非外部查询中的字段（d.dept_id）。

以上语句的原意是，查找每个部门中的最高月薪。为了实现这个功能，我们可以使用另一种子查询：横向子查询（LATERAL Subquery）。横向子查询允许派生表使用它所在的 FROM 子句中左侧的其他查询或者表。例如：

-- Oracle、MySQL 以及 PostgreSQL
SELECT d.dept_name "部门名称", t.max_salary AS "最高月薪"
FROM department d
CROSS JOIN
LATERAL (SELECT MAX(e.salary) AS max_salary
FROM employee e
WHERE e.dept_id = d.dept_id) t;

我们在 JOIN 之后指定了 LATERAL 关键字，此时子查询可以使用左侧部门表中的字段。查询返回的结果如下：

部门名称 |最高月薪 
--------|--------
行政管理部|30000.00
人力资源部|24000.00
财务部   |12000.00
研发部   |15000.00
销售部   |10000.00
保卫部   | 

Oracle、MySQL 以及 PostgreSQL 提供了横向子查询。Microsoft SQL Server 提供了相同的功能，但是实现的语法不同：

-- Microsoft SQL Server 和 Oracle
SELECT d.dept_name "部门名称", t.max_salary AS "最高月薪"
FROM department d
CROSS APPLY (SELECT MAX(e.salary) AS max_salary
FROM employee e
WHERE e.dept_id = d.dept_id) t;

其中，CROSS APPLY 是内连接查询的一种变体，允许右侧的子查询使用 FROM 子句中左侧查询或者表中的字段。该查询返回的结果和上一个示例相同。

除 CROSS APPLY 外，还有一个 OUTER APPLY，它是左外连接的一种变体，同样允许右侧的子查询使用 FROM 子句中左侧查询或者表中的字段。Microsoft SQL Server 和 Oracle 提供了这两种查询语法。

⚠️SQLite 目前不支持横向子查询。

EXISTS 运算符

EXISTS 运算符用于判断子查询结果的存在性。只要子查询返回了任何结果，就表示满足查询条件；如果子查询没有返回任何结果，就表示不满足查询条件。

例如，以下查询返回了拥有女性员工的部门：

SELECT d.dept_name AS "部门名称"
FROM department d
WHERE EXISTS (SELECT 1
FROM employee e
WHERE e.sex = '女'
AND e.dept_id = d.dept_id)
ORDER BY dept_name;

EXISTS 关键字之后是一个关联子查询，首先通过外部查询找到 d.dept_id，然后依次将每个 d.dept_id 传递给子查询，判断该部门是否存在女性员工。子查询一旦找到女性员工，就会立即返回结果，而无须遍历所有的员工数据。查询返回的结果如下：

部门名称
-------
研发部
财务部

查询结果表明“研发部”和“财务部”拥有女性员工。

ℹ️EXISTS 运算符只判断结果的存在性，因此子查询 SELECT 列表中的内容可以是任意字段或者表达式（通常可以使用一个常量值）。

另外，NOT EXISTS 可以执行与 EXISTS 相反的操作。如果我们想要查找不存在女性员工的部门，可以将上面的语句改写如下：

SELECT d.dept_name AS "部门名称"
FROM department d
WHERE NOT EXISTS (SELECT 1
FROM employee e
WHERE e.sex = '女'
AND e.dept_id = d.dept_id)
ORDER BY dept_name;

查询返回的结果如下：

部门名称
-----
人力资源部
保卫部 
行政管理部
销售部

虽然 [NOT] EXISTS 和 [NOT] IN 运算符都可以用于判断子查询返回的结果，但是它们之间存在一个重要的区别：[NOT] EXISTS 运算符只检查结果的存在性，[NOT] IN 运算符需要比较实际的值是否相等。

当子查询的结果中只有 NULL 值时，EXISTS 运算符仍然可以返回结果，NOT EXISTS 运算符则不返回结果。但是，此时 IN 和 NOT IN 运算符都不会返回结果，因为任何数据和空值进行比较的结果均未知。例如，以下语句用于查找没有女性员工的部门：

SELECT d.dept_name
FROM department d
WHERE NOT EXISTS (SELECT NULL
FROM employee e
WHERE e.dept_id = d.dept_id
AND sex = '女');

对于存在女性员工的部门，子查询中返回的数据为 NULL，NOT EXISTS 运算符不会返回结果，因此查询只会返回没有女性员工的部门。查询返回的结果如下：

部门名称
---------
行政管理部 
人力资源部 
销售部 
保卫部 

下面我们使用 NOT IN 运算符实现以上查询：

SELECT d.dept_name AS "部门名称"
FROM department d
WHERE d.dept_id NOT IN (3, 4, NULL);

财务部（dept_id=3）和研发部（dept_id=4）拥有女性员工，我们特意在 NOT IN 运算符后面增加了一个 NULL 值。该查询返回的结果为空，因为它等价于以下查询：

SELECT d.dept_name AS "部门名称"
FROM department d
WHERE d.dept_id != 3 
AND d.dept_id != 4
AND d.dept_id != NULL;

查询条件中最后一项的结果为“未知”，所以查询不会返回任何结果。