《数据库基础语法》11. 子查询：多表查询的另一种方式

楔子

到目前为止，我们的查询都是从单个表中获取数据。下面我们开始探讨一下如何从多个表中获取相关的数据。因为在关系数据库中，通常将不同的信息和它们之间的联系存储到多个表中。比如产品表、用户表、用户订单表、以及关联的订单明细表等。当我们想要查看某个订单时，需要同时从这几个表中查找关于订单的全部信息。

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

假设我们要计算年龄大于平均年龄的员工：

-- 一种笨办法就是先计算年龄的平均值，然后拿到这个平均值再去查询
SELECT AVG(age) FROM staff;  -- 37.0000

SELECT * FROM staff WHERE age > 37;

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

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

SELECT * FROM staff WHERE age > (SELECT AVG(age) FROM staff);

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

SQL 中的子查询可以分为以下三种类型：

• 标量子查询（Scalar Subquery）：返回单个值（一行一列）的子查询。上面的示例就是一个标量子查询。
• 行子查询（Row Subquery）：返回单行结果（一行多列）的子查询，标量子查询是行子查询的一个特例。
• 标表子查询（Table Subquery）：返回一个虚拟表（多行多列）的子查询，行子查询是表子查询的一个特例。

标量子查询

标量子查询的结果就像一个常量一样，可以用于 SELECT、WHERE、GROUP BY、HAVING 以及 ORDER BY 等子句中。我们计算一下员工的年龄和平均年龄之差。

SELECT id, age, CAST(age - (SELECT AVG(age) FROM staff) AS SIGNED) AS age1
FROM staff
LIMIT 10
/*
01010011868    25    -12
01010010306    32    -5
01010001867    44    7
01010007780    39    2
01010002647    33    -4
01010002350    30    -7
01010001563    34    -3
01010010898    27    -10
01010003113    47    10
01010007477    39    2
 */

估计有人会这么干：

SELECT id, age, CAST((age - AVG(age)) AS SIGNED) AS age1
FROM staff
LIMIT 10
-- 直接用 age - avg(age)，这样写虽然人很容易理解
-- 但是不好意思，这样写SQL不允许，因为一旦出现了聚合函数
-- 那么 SELECT 后面的字段要么出现在聚合函数中，要么出现在 GROUP BY 字句中

同理如果寻找年龄第二大的，我们可以这么做：

SELECT MAX(age) FROM staff
WHERE age < (SELECT MAX(age) FROM staff);  -- 60
-- 先把最大的age选出来，然后让age小于这个最大值，然后在剩余的记录中再选择最大值
-- 得到的不就是第二大的了吗
-- 但是这要求，最大值不能有重复，假设最大值是50，但是有两个50，这样的话选择就是第三大的了

-- 但是不管怎样，我们肯定不可以这么写
SELECT MAX(age) FROM staff
WHERE age < MAX(age);
-- 这样写是错的，先不说 MAX(age)中的 age 有可能是其它表中的 age
-- 即使是一张表的 age，也不可以这么写。
-- 因为 WHERE 是表过滤，WHERE 逻辑里面不能包含聚合，如果包含，那么聚合一定是子查询里面的聚合。

行子查询

行子查询可以当作一个一行多列的临时表使用，顾名思义就是返回一行。

我们以之前的 girl_info 和 girl_score 为例：

-- 选择 id 出现在 girl_score 中的 girl_info 表的记录
SELECT * FROM girl_info
WHERE id IN (SELECT id FROM girl_score);
/*
1002    古明地恋    15
1003    椎名真白    17
1004    芙兰朵露    400
1005    雾雨魔理沙    	
1006    坂上智代    19
1001    古明地觉    16
1001    古明地觉    21
 */
-- 当然我们这里是全部记录

表子查询

当子查询返回的结果包含多行、多列的时候，成为表子查询，表子查询通常用于查询条件或者FROM 子句中。

SELECT * FROM (SELECT id, score FROM girl_score WHERE id > 1002) AS g;
/*
1003	95
1004	81
1005	100
1006	86
 */

我们这里把子查询返回的结果直接当成一张表来用，当然标量子查询、行子查询也是可以这么做的，跟在from的后面充当一张表的作用。另外，如果是这么做的话，那么必须要起一个别名。

 

小心陷阱

对于 WHERE 中的子查询，需要注意返回的数据要进行匹配。比如：

-- 这个语句就是不合法的，因为id=的后面需要跟一个标量，会返回girl_info的id字段中和这个标量相等的值所以对应的记录
-- 而我们返回的是多条数据，所以不匹配
SELECT * FROM girl_info
WHERE id = (SELECT id FROM girl_score);

-- 这样也是不合法的，因为还是返回了多条
SELECT *
FROM girl_info
WHERE id = (SELECT id FROM girl_score WHERE id != 1002);

-- 合法
SELECT *
FROM girl_info
WHERE id = (SELECT id FROM girl_score WHERE id != 1002 AND id != 1001 AND id != 1003 AND id != 1004 AND id != 1005);
/*
这样是合法的，因为我们只保留了一条数据，所以返回的是标量
*/

-- 一般这种情况，我们会使用in，in后面需要跟一列
-- 把 = 改成in是没问题的
SELECT * FROM girl_info
WHERE id IN (SELECT id FROM girl_score);

-- 但是，下面的语句也是可以的
SELECT *
FROM girl_info
WHERE id IN (SELECT id FROM girl_score WHERE id != 1002 AND id != 1001 AND id != 1003 AND id != 1004 AND id != 1005);
-- 我们说，虽然只返回了一条数据，但是它即可以看成是标量，也可以看成行，只不过这个行只有一列数据


-- 同理，如下是不合法的
SELECT * FROM girl_info
WHERE id IN (SELECT id, age FROM girl_score);
/*
提示我们，子查询有太多的字段，前面的id是一个字段，但是我们子查询返回了两个
*/

因此在涉及子查询的时候，要小心，可以自己下去多尝试一下。

子查询返回的内容可以在很多地方使用，只要遵循之前的语法规范，比如 JOIN 是连接表，而子查询返回的内容也可以看成是一张表，那么它就可以跟在 JOIN 后面，只是当它作为表的时候需要起一个别名。

再比如子查询返回的内容可以看成是一个标量，那么标量能在什么地方用，子查询范返回的结果也可以在什么地方用，前提返回的得是一个标量。同理对于行、表也是一样的，根据返回的内容判断子查询是什么身份，该身份能在什么地方使用，那么子查询返回的结果就可以在什么地方使用。所以子查询能作用的返回很广，也正因为如此，才可以用SQL做更多的事情，如果不支持子查询，可以说，SQL 算是 "没了两条腿"。也正因为如此，SQL学好了也是很厉害的，因为表之间层层连接、子查询之间层层嵌套的逻辑也不是那么简单的。

子查询和普通查询本质上没什么两样，所以里面也可以嵌套 GROUP BY、JOIN、LIMIT 等逻辑。

 

ALL、ANY/SOME 运算符：

ALL 运算符一般与比较运算符（=、!=、<、<=、>、>=）结合，表示等于、不等于、小于、小于等于、大于或者大于等于子查询结果中的所有值。

SELECT * FROM girl_info
WHERE id >= all(SELECT id FROM girl_score); -- 1006	坂上智代	19
-- 因为girl_score中id的最大值为1006，girl_info中id的最大值也为1006，所以只有id=1006的记录返回
-- all要求必须和子查询中所有值都满足相应的关系，所以这里是选择girl_info中的id 大于等于 girl_score中的所有id的记录

ANY/SOME 运算符与比较运算符（=、!=、<、<=、>、>=）结合表示等于、不等于、小于、小于等于、大于或者大于等于子查询结果中的某个值即可。

SELECT * FROM girl_info
WHERE id > any(SELECT id FROM girl_score); 
/*
1002	古明地恋	15
1003	椎名真白	17
1004	芙兰朵露	400
1005	雾雨魔理沙	
1006	坂上智代	19
 */
-- girl_score中id最小的值为1001，因为girl_info中，只要id大于1001就满足条件
-- some和any一样，不再演示

 

EXISTS 操作符：

EXISTS 操作符用于判断子查询结果的存在性。如果子查询存在任何结果，EXISTS 返回真；否则，返回假。

我们将 girl_info 表修改一下，为了能看到效果：

1001	古明地觉	16
1002	古明地恋	15
1003	椎名真白	17
10040	芙兰朵露	400
10050	雾雨魔理沙	
1006	坂上智代	19
1001	古明地觉	16

我们将1004和1005后面加上了一个0
girl_score表不变

SELECT *
FROM girl_info AS gi
WHERE EXISTS(SELECT 1
             FROM girl_score AS gs
             WHERE gi.id = gs.id)
/*
1002	古明地恋	15
1003	椎名真白	17
1006	坂上智代	19
1001	古明地觉	16
1001	古明地觉	21
 */

我们来分析一下逻辑，我们exists只是判断子查询有没有返回内容，至于返回的内容是什么不关心，只要返回了东西即可。

我们先执行外部查询，找到 gi.id，然后传递给子查询，一旦在 gs 中找到个 gi.id 相等的 id，那么就会返回。我们返回的是1，我们说返回的是什么不重要，重要的是有没有返回。而一旦返回了，那么 EXISTS 函数的执行结果便为 true，那么 gi 的这条记录就是符合的。而 10040 和 10050 在 gs 的 id 字段中不存在，所以 EXISTS 函数执行结果为 false。所以这个和我们之前的 JOIN、也就是内连接之间没有什么区别，只不过我们用 EXISTS 和 子查询 重新实现了。

另外我们看到子查询当中也是可以使用外部查询的表的，比如我们这里的子查询使用了外部查询的 girl_info 表。

NOT EXISTS 执行相反的操作。

现在，我们知道 [NOT] EXISTS 和 [NOT] IN 都可以用于判断子查询返回的结果。但是它们之间存在一个重要的区别：[NOT] EXISTS 只检查存在性，[NOT] IN 需要比较实际的值是否相等。因此，当子查询的结果包含 NULL 值时，EXISTS 仍然返回结果，NOT EXISTS 不返回结果；但是此时 IN 和 NOT IN 都不会返回结果，因为 (X = NULL) 和 NOT (X = NULL) 的结果都是未知。其实说白了，因为 EXISTS 前面不需要加字段，不会进行比较，只能判断肚子里面的子查询是否返回了东西。

我们还可以在子查询中包含其他的子查询，实现嵌套子查询。我们之前说过了。

小结

子查询语句为我们提供了在一个查询中访问多个表的另一种方式，很多时候可以实现与连接查询相同的效果。本篇我们讨论了各种形式的子查询，包括相关的操作符和注意事项。