一、标量子查询与多行子查询

子查询先算自己那一段，算完把结果交给外面用。 只返回一行一列的叫标量子查询，凡是能写一个值的地方都能放它。 教材的典型例子是「查价格高于平均价的商品」：先算平均价，再拿它当门槛。 没有子查询就得跑两趟，先查出一个数，再拼一句带数字的 SQL。 多行子查询返回一整列，这时外面不能用 =，得用 IN、EXISTS 或者 ANY／ALL。 这是最容易错的地方：写成 = 一般会报错，但也有引擎会默默取第一行。 沙箱就属于后面那种：WHERE price > (SELECT price FROM tb_goods) 不报错， 它取了第一行当那个值，于是给出 4980 行。 MySQL 会直接报 Subquery returns more than 1 row，不替你挑一行。 所以同一句写错的 SQL，沙箱里看着跑通了，换到 MySQL 立刻炸。

二、IN 与 EXISTS

IN 的语义是「值在这个集合里」，EXISTS 的语义是「子查询找得到记录」。 两者在很多场景下能互相改写，结果一样。 区别在「什么时候停」：EXISTS 找到第一条就可以停，IN 通常要把集合算完。 所以子查询结果集很大时，EXISTS 往往更快；结果集很小，两者差不多。 这一条只是方向，别当定律。 现代优化器会把 IN 改写成半连接，两边可能给出完全一样的计划，只能看执行计划确认。 EXISTS 还有一个副产品：它不关心子查询 SELECT 后面写什么。 写 SELECT 1、SELECT *、SELECT 一整列 都一样，只判断「有没有行」。

三、NOT IN 的空集陷阱

这一节是整章最值钱的地方。 NOT IN 的子查询里只要有一个空值，整句就返回 0 行。 原因是 NOT IN 会被展开成「不等于第一个、也不等于第二个、也不等于空」。 前两个比得出来，第三个比不出来——x <> NULL 的结果是「未知」，不是「真」。 只要有一行「未知」，整个 AND 就不再为真，于是这一行被丢掉。 结果就是：一行都不剩，而语句不报错。

第二行加了 IS NOT NULL 就正常了：3891 行， 等于 5000 减 974（两个被拉黑类别）再减 135（类别为空）。 第三行比第二行多 135 行，多的是「类别为空」那批商品。 它们类别是空的，NOT EXISTS 判「拉黑表里找不到对应记录」，算真。 而 NOT IN 判的是「不等于」，还是那个「未知」，所以那 135 行反而被丢掉了。 同一批数据，三种写法给出三个数，只有你要的那个才叫对。 结论：子查询里可能有空值时，NOT IN 一律换成 NOT EXISTS。 这一条比记住语法重要得多，生产上的「查不出数据」有一大半出在这里。

四、FROM 里的派生表，与 SELECT 列表里的相关子查询

FROM 后面跟子查询，相当于临时造出一张表，这种表叫派生表。 外面那句可以对它再筛、再排、再连，跟真表一样。 沙箱里不给别名也能跑，但 MySQL 会报 Every derived table must have its own alias。 所以写的时候一律带上别名，省得换库就炸。 派生表的价值是「分两步算」：先算出一个中间结果，再对它二次筛选。 很多「先分组再筛组」的需求，写成派生表比写 HAVING 更好读。 SELECT 列表里放子查询是另一个用法，它给每一行算一个附加列。 这种子查询引用了外面的列，所以外面每行都得重跑一次，这就是相关子查询。 代价是「外层行数乘以内层单次代价」。 内层是 11 行的类别表时看不出来，内层是 2000 行的订单表时就差了一百多倍。 能改成连接的就改成连接：连接是一趟扫完，相关子查询是一行一趟。

五、ANY 与 ALL 的等价改写

教材还讲了 ANY 和 ALL，但沙箱的 SQLite 没有这两个关键字，照抄直接语法错。 它们的语义可以完全用 MIN／MAX 表达，记住这张对照就不会被绕晕。

> ANY 的意思是「比其中某一个大就行」，所以门槛是那一列的最小值。 > ALL 是「比每一个都大」，门槛是最大值。 换成小于号方向正好对调：< ANY 的门槛是最大值，< ALL 的门槛是最小值。 因为「小于某一个」只要小于最大的那个就算，而「小于全部」得小于最小的那个。 还有一对对应关系：= ANY 就是 IN，<> ALL 就是 NOT IN。 所以上一篇那个空值陷阱，写成 <> ALL 一样会踩。

六、动手 Demo：子查询的四种位置，和 NOT IN 那个空集陷阱

为了把空值陷阱造干净，这里额外建了一张三行的「拉黑表」：类别 3、类别 7、还有一个空值。 那个空值就是整段的引信。

import sqlite3

c = sqlite3.connect(u"d040.db")
c.execute(u"CREATE TABLE tb_black (cat_id INTEGER)")
c.executemany(u"INSERT INTO tb_black VALUES (?)", [(3,), (7,), (None,)])

# ① 标量子查询
print(c.execute(u"SELECT AVG(price) FROM tb_goods").fetchone())
print(c.execute(u"SELECT COUNT(*) FROM tb_goods WHERE price > "
                u"(SELECT AVG(price) FROM tb_goods)").fetchone())

# ② 空集陷阱：三种写法三个数
for sql in [u"SELECT COUNT(*) FROM tb_goods WHERE cat_id NOT IN "
            u"(SELECT cat_id FROM tb_black)",
            u"SELECT COUNT(*) FROM tb_goods WHERE cat_id NOT IN "
            u"(SELECT cat_id FROM tb_black WHERE cat_id IS NOT NULL)",
            u"SELECT COUNT(*) FROM tb_goods WHERE NOT EXISTS "
            u"(SELECT 1 FROM tb_black b WHERE b.cat_id = tb_goods.cat_id)"]:
    print(c.execute(sql).fetchone()[0])

# ③ 相关子查询 vs 连接
c.execute(u"SELECT id, (SELECT COUNT(*) FROM tb_order o "
          u"WHERE o.goods_id = tb_goods.id) FROM tb_goods").fetchall()
c.execute(u"SELECT g.id, COUNT(o.id) FROM tb_goods g "
          u"LEFT JOIN tb_order o ON o.goods_id = g.id GROUP BY g.id").fetchall()

实跑输出：

--------------------------------------------------------------------
第四十课：子查询的四种位置，和 NOT IN 那个空集陷阱
--------------------------------------------------------------------

  **教材 9.5 讲子查询。子查询就是「一条 `SELECT` 套在另一条里面」。**
  **它按放的位置分四种，用法各不相同。**
  **这一课四种都跑一遍，最后专讲 `NOT IN` 遇上空值会给出 0 行这件事。**

  一、标量子查询：里面只返回一个值
  --------------------------------------------------------------------

  项                  值
  -------------------------
  全表平均价          99.55
  高于平均价的商品数  2466
  全表行数            5000
  **子查询先算出一个数，外面那句再把它当常量用。**
  **没有子查询就得跑两趟：先查平均数，再拼一句带数字的 SQL。**
  **这种「只返回一行一列」的子查询叫标量子查询，凡是能写一个值的地方都能放它。**
  **顺手验一件事：如果括号里返回的不是一个值，而是一整列呢？**
  **沙箱给了 4980 行，没报错——它默默取了第一行当那个「值」。**
  **MySQL 在这里会报 `Subquery returns more than 1 row`，不会替你挑一行。**
  **所以同一句写错的 SQL，沙箱里看着跑通了，换到 MySQL 立刻炸。**

  二、多行子查询：IN 与 EXISTS
  --------------------------------------------------------------------
  cat_id
  ------
  3
  7
  NULL
  3 rows in set

  写法               结果行数  含义
  -------------------------------------------------
  `IN (子查询)`      974       类别在拉黑表里的商品
  `EXISTS (子查询)`  974       同一批商品，写法不同
  **这张拉黑表里只有三条：类别 3、类别 7，还有一条是空的。**
  **第三条那个空值是故意留的——它马上要把下面一句打回 0 行。**

  三、NOT IN 的空集陷阱
  --------------------------------------------------------------------

  写法                                结果行数
  --------------------------------------------
  `NOT IN (子查询)` 原样              0
  `NOT IN (子查询)` 加 `IS NOT NULL`  3891
  `NOT EXISTS (子查询)`               4026
  **第一行给 0 行，而拉黑表里只拉黑两个类别，本该有几千行。**
  **原因是那句 `NOT IN` 要逐行比「这个类别不等于 3、也不等于 7、也不等于空」。**
  **前两个比得出来，第三个比不出来——`x <> NULL` 的结果是「未知」，不是「真」。**
  **只要有一行「未知」，整个 `AND` 就不再为真，于是这一行被丢掉。**
  **所以 `NOT IN` 的子查询里只要有一个空值，结果必然是 0 行。**
  **这不是 bug，是三值逻辑算出来的必然结果。**
  **第二行加了 `IS NOT NULL` 就正常了：3891 行。**
  **它等于 5000 减去 974 行（两个被拉黑类别），再减去 135 行（类别为空）。**
  **第三行 `NOT EXISTS` 又多了 135 行——多的是「类别为空」那批商品。**
  **因为它们类别是空的，`NOT EXISTS` 判「拉黑表里找不到对应记录」，算真。**
  **而 `NOT IN` 判的是「不等于」，还是那个「未知」。**
  **结论：子查询里可能有空值时，`NOT IN` 一律换成 `NOT EXISTS`。**
  **这一条比记住语法重要得多，生产上的「查不出数据」有一大半出在这里。**

  四、FROM 里的子查询：派生表
  --------------------------------------------------------------------
  cat_id n
  ----------
  1      487
  3      487
  4      487
  7      487
  10     487
  5 rows in set
  **`FROM` 后面跟一个子查询，相当于临时造出一张表，这种表叫派生表。**
  **外面那句可以对它再筛、再排、再连，跟真表一样。**
  **沙箱里不给别名也能跑，但 MySQL 会报 `Every derived table must have its own alias`。**
  **所以写的时候一律带上 `t`，省得换库就炸。**

  五、SELECT 列表里的相关子查询，和它的代价
  --------------------------------------------------------------------
  id cat
  -------
  1  数码
  2  家电
  3  服饰
  3 rows in set

  内层那张表             相关子查询    改成连接
  ----------------------------------------------
  `tb_cat`（11 行）      6.35 毫秒     5.56 毫秒
  `tb_order`（2000 行）  1123.40 毫秒  8.96 毫秒
  **第一行差得不多，第二行差了 125 倍。同一个写法，差距由内层表的规模决定。**
  **原因很简单：这种子查询引用了外面的列，所以外层每一行都得把它重跑一次。**
  **外层 5000 行，内层就要跑 5000 趟，内层多大就乘多少。**
  **所以它叫相关子查询——「相关」就是指它和外面那一行绑在一起，没法提前算完。**
  **反过来，不引用外层列的写法叫不相关子查询，那种只算一次，代价小得多。**
  **能改成连接的都改成连接。**
  **连接是一趟扫完，相关子查询是一行一趟，这就是那个倍数的来源。**

  六、ANY 和 ALL：教材有，沙箱没有
  --------------------------------------------------------------------
  **照抄会报错：`near "SELECT": syntax error`——SQLite 没有 `ANY` 和 `ALL` 这两个关键字。**

  教材写法          等价改写               结果行数
  -------------------------------------------------
  `> ANY (子查询)`  `> (SELECT MIN(...))`  4988
  `> ALL (子查询)`  `> (SELECT MAX(...))`  13
  **`> ANY` 的意思是「比其中某一个大就行」，所以门槛是那一列的最小值。**
  **`> ALL` 是「比每一个都大」，门槛是最大值。**
  **换成小于号方向正好对调：`< ANY` 的门槛是最大值，`< ALL` 的门槛是最小值。**
  **因为「小于某一个」只要小于最大的那个就算，而「小于全部」得小于最小的那个。**
  **顺带一个对应关系：`= ANY` 就是 `IN`，`<> ALL` 就是 `NOT IN`。**
  **所以 `NOT IN` 那个空值陷阱，`<> ALL` 一样有。**

陷阱那一段的三个数是 0、3891、4026，这是这一课的核心。 第一行那个 0 会让人以为「数据有问题」，其实数据一点问题都没有。 第二行和第三行差的 135，正是「类别为空」那批商品。 它们在 NOT IN 里被判成「未知」丢掉，在 NOT EXISTS 里被判成「没被拉黑」留下。 相关子查询那一段给了两组对比：内层是 11 行的类别表时是 6.47 对 5.32 毫秒， 内层是 2000 行的订单表时是 1137.70 对 9.75 毫秒。 同一个写法，一个差两成，一个差 117 倍。 所以「相关子查询慢」这句话要带上条件：慢在内层也被放大。 ANY／ALL 那一段照抄会报 near "SELECT": syntax error，这是 SQLite 语法层面的空缺， 不是函数没注册。

30 秒实验

1. 把拉黑表里的空值删掉，看第一行从 0 变成 3891。
2. 把 NOT EXISTS 换成 NOT IN，确认它给出 0。
3. 把相关子查询的内层换成 tb_order，看耗时从毫秒跳到秒。

常见误区

1. 用 NOT IN 查「不在某个集合里」，碰上带空值的子查询，拿到 0 行还以为没数据。
2. 在 SELECT 列表里写相关子查询，表一大就慢到没法用，其实一句连接就够。
3. 照抄 ANY／ALL，没意识到换个引擎就不认这两个关键字。

下一篇 9.6 讲把两段查询的结果竖着接起来：UNION，和去重的那一笔开销。

• ☐ （本篇完，下一篇 9.6 合并查询结果）

相关笔记

• 连接查询
• 聚合函数查询
• 添加数据
• 🛢️ MySQL从入门到精通 · 索引