5.3 子查询 · 波仔的 AI 编程实战课

📄 一句话概括

子查询就是「括号里再写一条查询」，它的结果当成一个值、一个列表、或者一张表用。 Demo 里 WHERE height > (SELECT AVG(height) ...) 给 6 个人； 子查询当一列时，每行算一次自己的数（张伟 1 单、王芳 2 单）； IN 配子查询给 4 人，EXISTS 给 3 人。 最容易踩的一脚： = (SELECT ...) 而子查询返回多行——这里不报错、悄悄用第一行，MySQL 直接报错。

📝 本章和前面几章的关系

前两篇的子句都在一条语句里打转，这一篇把两条语句拼起来。 子查询是「查询的查询」，也是后面视图、存储程序的地基。 本章练习里那种「先算平均，再找高于平均的」题，全靠它。

一、单值子查询：先算一个数，再拿它当条件

括号里那句先跑，得到一个数，再当普通值用。 WHERE height > (SELECT AVG(height) FROM students) 就是「高于平均身高的」。 这是子查询最简单也最常用的一种。 它一次算完，外层拿到的就是一个常量，不随行变化。 注意它只能返回一行一列。 返回多列或多行，就得换成 IN、EXISTS，或者把子查询挪到 from 里当表。

二、子查询当一列：关联子查询

把子查询写在 select 后面，它就变成结果集里的一列： SELECT s.name, (SELECT COUNT(*) FROM orders o WHERE o.student_id = s.id) AS n FROM students s。 括号里那句引用了外层的 s.id，所以外层每一行都要重跑一次。 这种叫「关联子查询」， Demo 里张伟 1 单、王芳 2 单、李娜 2 单、刘洋 1 单。 它慢就慢在这儿：外层 12 行，子查询就执行 12 次。 外层上万行时，这个写法会慢得离谱，该改成 join 加 group by。

三、多值子查询：配 in，也配 exists

子查询返回一列值时，外层不能用 =，要用 in。 Demo 里 class_id IN (SELECT id FROM classes WHERE teacher = '马老师') 给 4 人。 exists 是另一种用法：它只问「子查询有没有返回行」，不关心返回什么，所以里面写 SELECT 1 就够了。 not in 有个坑：子查询结果里只要有 null，整条给 0 行。因为「不等于 null」永远不成立。 这种坑到第 10 章讲 null 时还会见。

四、子查询当表：from 后面跟一个括号

SELECT name FROM (SELECT name, height FROM students WHERE height > 175) AS t—— 括号里的结果当成一张临时表。 别名不能省，from (…) 后面必须跟一个名字，否则报错。 这一点和 select 里的别名不一样，那里可以省。 这种写法在做多层统计时很常用：先算一层明细，再在结果上算第二层（比如先算每人总分，再算平均总分）。

五、最容易踩的一脚：拿 = 接一个多行子查询

WHERE class_id = (SELECT id FROM classes) 里，子查询返回三个 id，而 = 只接受一个值。 这是逻辑错，不是语法错。 Demo 里这句给了 4 行，用的是子查询的第一行（id = 1）。 MySQL 会直接报 Subquery returns more than 1 row。 这里不报，是 SQLite 的宽松行为。 所以「= 后面接子查询」只在你能确认它一定只返回一行时才安全。 不确定就用 in， 或者给子查询加 limit 1 把意图写明白。

六、动手 Demo：从「高于平均」到「每人的单数」，再到撞一次宽松行为

Demo 六段：单值、当列、配 in、配 exists、当表、以及最后那次 = 撞多行。 前三段是常用写法，后三段是「会写但要知道代价和边界」的写法。

import _q46 as Q
c = Q.con()

# 单值子查询：先算出一个数
c.execute("SELECT name, height FROM students "
          "WHERE height > (SELECT AVG(height) FROM students)")
# 子查询当一列（关联子查询）
c.execute("SELECT s.name, (SELECT COUNT(*) FROM orders o "
          "WHERE o.student_id = s.id) AS n FROM students s LIMIT 4")
# 多值子查询配 in
c.execute("SELECT name FROM students WHERE class_id IN "
          "(SELECT id FROM classes WHERE teacher = '马老师')")
# exists：只问有没有
c.execute("SELECT s.name FROM students s WHERE EXISTS "
          "(SELECT 1 FROM orders o WHERE o.student_id = s.id "
          "AND o.amount > 200)")
# 子查询当表
c.execute("SELECT name FROM (SELECT name, height FROM students "
          "WHERE height > 175) AS t")
# = 接多行子查询：这里不报错，MySQL 报错
c.execute("SELECT name FROM students WHERE class_id = "
          "(SELECT id FROM classes)")

实跑输出：

--------------------------------------------------------------------
第二十二课：子查询——把一条查询的结果喂给另一条
--------------------------------------------------------------------

  单值子查询：先算出一个数，再拿它当条件
  SQL> SELECT name, height FROM students WHERE height > (SELECT AVG(height) FROM students)
  name     height
  ----------------
  张伟     172.50
  刘洋     178.30
  杨帆     175.10
  黄磊     180.20
  周涛     174.00
  徐明     169.70
  （6 行）

  **括号里那句先跑，得到一个 168.44，再拿它去比。** **高于平均身高的 6 个人全出来了。**
  **这叫「单值子查询」**：子查询只返回一行一列，可以当普通值用。

  子查询当一列：每行算一个自己的数
  SQL> SELECT s.name, (SELECT COUNT(*) FROM orders o WHERE o.student_id = s.id) AS n
       FROM students s LIMIT 4
  name     n
  --------------
  张伟     1
  王芳     2
  李娜     2
  刘洋     1
  （4 行）

  **括号里那句引用了外层的 `s.id`，所以每一行都重新算一遍。**
  **张伟 1 单、王芳 2 单、李娜 2 单、刘洋 1 单。**
  **这种「引用外层列」的子查询叫关联子查询**，**它比普通子查询慢**——外层有多少行，它就执行多少次。

  多值子查询：返回一列值，配 in 用
  SQL> SELECT id, name, teacher FROM classes WHERE teacher = '马老师'
  id     name     teacher
  ------------------------
  1      一班     马老师
  （1 行）

  SQL> SELECT name FROM students WHERE class_id IN (SELECT id FROM classes
       WHERE teacher = '马老师')
  name
  -----
  张伟
  王芳
  杨帆
  周涛
  （4 行）

  **子查询给的是「一个值的列表」，所以配 `in` 用**，不能直接用 `=`。
  **MySQL 还支持 `any`／`all`，本沙箱不支持这两个**，教材里遇到它们照原样记，这里跑不了。
  **`in` 还一个坑**：`not in` 碰上子查询结果里有 null，**整条给 0 行**。
  **因为「不等于 null」永远不成立。** 这个坑到第 10 章讲 `null` 时还会见。

  关联子查询配 exists：只问「有没有」
  SQL> SELECT s.name FROM students s WHERE EXISTS (SELECT 1 FROM orders o
       WHERE o.student_id = s.id AND o.amount > 200)
  name
  -----
  刘洋
  陈静
  孙悦
  （3 行）

  **`exists` 只关心「子查询有没有返回行」，不关心返回什么**，所以里面写 `SELECT 1` 就够了。
  **三张订单金额超过 200：324.00 的孙悦、299.00 的陈静、275.80 的刘洋。**
  **所以这三个人被选出来。**
  **`exists` 通常比 `in` 快**——它找到第一条就停，`in` 要把列表整个算完。
  **但优化器现在都很聪明，别凭这句口诀就乱改。**

  子查询当表：from 后面跟一个括号
  SQL> SELECT name FROM (SELECT name, height FROM students WHERE height > 175) AS t
  name
  -----
  刘洋
  杨帆
  黄磊
  （3 行）

  **括号里的结果当成一张临时表，起了别名 `t`，外面再查它。** **这种写法在做多层统计时很常用**——
  先算一层，再在结果上算第二层。
  **别名不能省**：`from (…)` 后面必须跟一个名字，否则报错。

  最容易踩的一脚：拿 = 接一个多行子查询
  **`WHERE class_id = (SELECT id FROM classes)` 里，子查询返回三个 id**，而 `=` 只接受一个值。

  SQL> SELECT name FROM students WHERE class_id = (SELECT id FROM classes)
  **这里给 4 行，用的是子查询的第一行（id = 1）。**
  **MySQL 会直接报 `Subquery returns more than 1 row`。** **这里不报，是 SQLite 的宽松行为。**

  **所以「`=` 后面接子查询」只有在你确认它一定只返回一行时才安全。**
  **不确定就用 `in`，或者给子查询加 `limit 1` 把意图写明白。**

第一段单值。 6 个人高于 168.44，子查询先算出一个数，外层拿它比。 第二段当列。 每行算一次自己的订单数——这就是关联子查询的代价。 第三段配 in。 先查出一班的 id，再用它筛学生，给 4 人。 第四段 exists。 3 个人有金额过 200 的订单：刘洋、陈静、孙悦。 第五段当表。 三个人高于 175，外面再查这张临时表。 第六段 = 撞多行。 这里给 4 行不报错，MySQL 会直接报错。 这一处差别要记住，跨库的代码不能依赖它。

30 秒实验

WHERE height > (SELECT AVG(height) FROM students) 里的子查询，外层 12 行它会跑几次？
把 IN (SELECT ...) 换成 = (SELECT ...)，会出什么事？先想再跑。
EXISTS 里 SELECT 1 换成 SELECT *，结果会变吗？

常见误区

拿 = 接一个可能返回多行的子查询。 这里悄悄用第一行，MySQL 报错， 同一个 SQL 在两边的行为完全不同。
在 select 里写关联子查询跑大表。 外层多少行就执行多少次，上万行时慢到没法用，该改成 join 加 group by。
not in 配子查询时不检查 null。 子查询结果里只要有 null，整条给 0 行，而且不报错。

自测三问（点开对答案）

1. 单值子查询和关联子查询的区别是什么？ —— 单值子查询一次算完，外层当常量用；关联子查询引用了外层列，外层每行都要重跑一次。 2. 子查询返回一列值时，为什么不能用 `=`？ —— `=` 只接受一个值。返回多行时本沙箱悄悄用第一行，MySQL 直接报错。 3. `exists` 里为什么写 `SELECT 1` 就够了？ —— `exists` 只关心子查询有没有返回行，不关心返回什么。

查询这块到这里收尾：条件、排序、分组、模糊、随机、子查询都有了。 下一篇换个方向——MySQL 内置的那些函数：数学、字符串、时间、排名、加密。

☐ （本篇完，下一篇 6.1 内置函数简介）