前端修炼小册

子查询语法

# 子查询语法

在有些场景中，我们需要使用两条或多条查询语句，而恰好第二条查询语句的搜索条件其实是用到了第一条查询语句的查询结果。为了书写简便，我们可以把这两条语句合并到一条语句中，从而减少了把第一条查询语句的结果复制粘贴到第二条查询语句中的步骤。

在书写上，只需要把第二条查询语句用小括号 () 扩起来作为一个操作数放到第一条的搜索条件处，这样就起到了合并两条查询语句的作用。

小括号中的查询语句也被称为子查询或者内层查询，使用内层查询的结果作为搜索条件的查询称为外层查询。如果在一个查询语句中需要用到更多的表的话，那么在一个子查询中可以继续嵌套另一个子查询，在执行查询语句时，将按照从内到外的顺序依次执行这些查询。

小贴士

所有的子查询都必须用小括号扩起来，否则是非法的。

# 标量子查询

如果子查询的结果只有一个值，这种子查询称之为标量子查询。比如下面这个，子查询的结果是一个学号（number）：

SELECT * FROM student_score WHERE number = (SELECT number FROM student_info WHERE name = '张星星');
1

因为标量子查询单纯的代表一个值，所以它可以作为表达式的操作数来参与运算，它除了用在外层查询的搜索条件中以外，也可以被放到查询列表处，比如这样：

SELECT (SELECT number FROM student_info WHERE name = '张星星') AS 学号;
1

标量子查询单纯的代表一个值，由标量子查询作为的操作数组成的搜索条件只要符合表达语法就可以。比如来查询学号大于 "张星星" 的学号的学生成绩，可以这么写：

SELECT * FROM student_score WHERE number > (SELECT number FROM student_info WHERE name = '张星星');
1

# 列子查询

如果子查询语句的结果集中并不是一个单独的值，而是一个列，该子查询也被称之为列子查询。

因为列子查询得到的结果是多个值，相当于一个列表。那么就可以使用 IN 和 NOT IN 操作符搭配子查询的结果组成表达式来作为外层查询的搜索条件的。

就像这样一个场景：查询 "计算机科学与工程" 专业的学生的成绩：

SELECT * FROM student_score WHERE number IN (SELECT number FROM student_info WHERE major = '计算机科学与工程');
1

# 行子查询

有列子查询，自然就有行子查询。只要子查询的结果集中最多只包含一条记录，而且这条记录中有超过一个列的数据（如果该条记录只包含一个列的话，该子查询就成了标量子查询），那么这个子查询就可以被称之为行子查询。

SELECT * FROM student_score WHERE (number, subject) = (SELECT number, '高等数学' FROM student_info LIMIT 1);
1

看上面这句查询语句，子查询的查询列表是 number（列名）, '高等数学'（常数），在子查询语句中加了 LIMIT 1 这个子句，意味着子查询最多只能返回一条记录，所以该子查询就可以被看作一个行子查询。

这句查询语句表达的语义就是：先获取到子查询的执行结果，然后再执行外层查询，如果 student_score 中记录的 number 等于子查询结果中的 number 列并且 subject 列等于子查询结果中的 '高等数学'，那么就将该记录加入到结果集。

因为子查询执行后产生的结果集是一个行（包含 2 个列），所以外层查询里用作等值比较的另一个操作数也得是 2 个值，本例中就是 (number, subject)（并且必须用小括号 () 扩住，否则会产生歧义）。

小贴士

在想要得到标量子查询或者行子查询，但又不能保证子查询的结果集只有一条记录时，应该使用 LIMIT 1 子句来限制记录数量。

# 表子查询

如果子查询结果集中包含多行多列，那么这个子查询也可以被称之为表子查询，比如这样：

SELECT * FROM student_score WHERE (number, subject) IN (SELECT number, '高等数学' FROM student_info WHERE major = '计算机科学与工程');
1

在这个例子中的子查询执行之后的结果集中包含多行多列，所以可以被看作是一个表子查询。

# EXISTS 和 NOT EXISTS 子查询

有时候外层查询并不关心子查询中的结果是什么，而只关心子查询的结果集是不是为空集，这时可以用到下边这两个操作符：

操作符 示例 描述
EXISTS EXISTS (SELECT ...) 当子查询结果集不是空集时表达式为真
NOT EXISTS NOT EXISTS (SELECT ...) 当子查询结果集是空集时表达式为真

举个例子：

mysql> SELECT * FROM student_score WHERE EXISTS (SELECT * FROM student_info WHERE number = 20200108);
Empty set (0.00 sec)

mysql>
1
2
3
4

其中子查询的意思是在 student_info 表中查找学号为 20200108 的学生信息，很显然并没有该学生，所以子查询的结果集是一个空集，于是 EXISTS 表达式的结果为 FALSE，所以外层查询也就不查了，直接返回了一个 Empty set，表示没有结果。

NOT EXISTS 的用法同理。

# 不相关子查询和相关子查询

所谓不相关子查询，就是子查询和外层查询都没有依赖关系，子查询可以独立运行并产生结果之后，再拿结果作为外层查询的条件去执行外层查询。

而有时候我们需要在子查询的语句中引用到外层查询的值，这样的话子查询就不能当作一个独立的语句去执行，这种子查询被称为相关子查询。

比如说我们想查看一些学生的基本信息，但是前提是这些学生在 student_score 表中有成绩记录，那可以这么写：

mysql> SELECT number, name, id_number, major FROM student_info WHERE EXISTS (SELECT * FROM student_score WHERE student_score.number = student_info.number);
+----------+-----------+--------------------+--------------------------+
| number   | name      | id_number          | major                    |
+----------+-----------+--------------------+--------------------------+
| 20220101 | 张星星    | 158177199901044792 | 计算机科学与工程         |
| 20220102 | 王二狗    | 151008199801178529 | 计算机科学与工程         |
| 20220103 | 陈珊珊    | 17156319980116959X | 软件工程                 |
| 20220104 | 李思思    | 141992199701078600 | 软件工程                 |
+----------+-----------+--------------------+--------------------------+
4 rows in set (0.00 sec)

mysql>
1
2
3
4
5
6
7
8
9
10
11
12

小贴士

student_info 和 student_score 表里都有 number 列，所以在子查询的 WHERE 语句中书写 number = number 会造成二义性，也就是让服务器懵逼，不知道这个 number 列到底是哪个表的。为了区分，在列名前边加上了表名，并用点 . 连接起来，这种显式的将列所属的表名书写出来的名称称为该列的全限定名。

上边子查询的 WHERE 语句中用了列的全限定名：student_score.number = student_info.number。

这条查询语句可以分成这么两部分来理解：

  • 我们要查询学生的一些基本信息。
  • 这些学生必须符合这样的条件：必须有成绩记录保存在 student_score 表中。

所以这个例子中的相关子查询的查询过程是这样的：

  • 先执行外层查询获得到 student_info 表的第一条记录，发现它的 number 值是 20220101。把 20220101 当作参数传入到子查询，此时子查询的意思是判断 student_score 表的 number 字段是否有 20220101 这个值存在，子查询的结果是该值存在，所以整个 EXISTS 表达式的值为 TRUE，那么 student_info 表的第一条记录可以被加入到结果集。
  • 再执行外层查询获得到 student_info 表的第二条记录，发现它的 number 值是 20220102，与上面的步骤相同，student_info 表的第二条记录也可以被加入到结果集。
  • 与上面类似，student_info 表的第三条记录也可以被加入到结果集。
  • 与上面类似，student_info 表的第四条记录也可以被加入到结果集。
  • 再执行外层查询获得到 student_info 表的第五条记录，发现它的 number 值是 20220105，把 20220105 当作参数传入到它的子查询，此时子查询的意思是判断 student_score 表的 number 字段是否有 20220105 这个值存在，子查询的结果是该值不存在，所以整个 EXISTS 表达式的值为 FALSE，那么 student_info 表的第五条记录就不被加入结果集中。
  • 与上一步骤类似，student_info 表的第六条记录也不被加入结果集中。
  • student_info 表没有更多的记录了，结束查询。

所以最后的查询结果是上面展示的 4 条记录。

# 对同一个表的子查询

不只是在涉及多个表查询的时候会用到子查询，在只涉及单个表的查询中有时也会用到子查询。

比如我们想看看在 student_score 表的 '高等数学' 这门课的成绩中，有哪些超过了平均分的记录，根据字面意思第一反应是这么写：

mysql> SELECT * FROM student_score WHERE subject = '高等数学' AND score > AVG(score);
ERROR 1111 (HY000): Invalid use of group function
mysql>
1
2
3

很抱歉，报错了。为啥呢？因为聚集函数是用来对分组做数据统计的（如果没有 GROUP BY 语句那么意味着只有一个分组），而 WHERE 子句是以记录为单位来执行过滤操作的，在 WHERE 子句执行完成之后才会得到分组，也就是说：聚集函数不能放到 WHERE 子句中。

如果想实现上面的需求，就需要搞一个 student_score 表的副本，就相当于有了两个 student_score 表，在一个表上使用聚集函数统计，统计完了之后拿着统计结果再到另一个表中进行过滤，这个过程可以这么写：

mysql> SELECT * FROM student_score WHERE subject = '高等数学' AND score > (SELECT AVG(score) FROM student_score WHERE subject = '高等数学');
+----------+--------------+-------+
| number   | subject      | score |
+----------+--------------+-------+
| 20220101 | 高等数学     |    78 |
| 20220102 | 高等数学     |   100 |
+----------+--------------+-------+
2 rows in set (0.00 sec)

mysql>
1
2
3
4
5
6
7
8
9
10

我们使用子查询先统计出了 '高等数学' 这门课的平均分，然后再到外层查询中使用这个平均分组成的表达式来作为搜索条件去查找大于平均分的记录。

（完）

连接查询语法