📑 本页目录（点开跳转）

🎯 一句话
🧩 一、三种形状：按「它返回什么」分类
🔁 二、相关 vs 非相关：判据是「单独拎出来能不能跑」
🎚️ 三、⭐ 行值：整行一起比，不是逐列比
🚦 四、IN / EXISTS / JOIN：同一个问题，三种写法
💀 五、三选一里唯一写死的一格：「不在里面」不用 NOT IN
🧯 六、剩下三个容易踩的
🔗 这一章连到哪里
✅ 检查点
🛑 可以停在这里

05 · 子查询的三种形状，和 IN / EXISTS / JOIN 选哪个

⏱ 100 分钟 ｜ ⭐ 06b 那个复合游标 (updated_at, id) < (?, ?) 到底是什么 —— 它是一个「行值」

🎯 一句话

子查询只有三种形状 —— 一个值、一行、一张表 —— 形状决定了它能写在哪；而 IN / EXISTS / JOIN 三选一的判据只有一句话：右表的列你到底要不要。不要就 EXISTS，要就 JOIN。这一条判据能挡掉 02 章那个把 SUM 从 150 变成 250 的扇出。

🧩 一、三种形状：按「它返回什么」分类

子查询就是写在另一条查询里的查询。它没有第四种分类法，只有三种形状：

形状	返回	长什么样
标量子查询	一行一列 = 一个值	`(SELECT MAX(updated_at) FROM conv)`
行子查询	一行多列 = 一整行	`(SELECT updated_at, id FROM conv ORDER BY … LIMIT 1)`
表子查询	多行（可以多列）	`(SELECT id FROM conv WHERE uid = 7)`

⭐ 判据不是「它自己长什么样」，是「它出现的位置要求它是什么形状」。 位置定形状：

位置	要求的形状
`SELECT` 列表里、`ORDER BY` 里、和一个列比大小的右边	必须标量
和一个行值比较（`(a, b) = (…)`）	必须行，且列数、顺序都要对上
`FROM` 后面、`IN` / `NOT IN` 后面、`EXISTS` 后面	表（`IN` 那个还要求只有一列）

⭐ FROM 里的表子查询顺便解决了 01 章第二节那个 ①「WHERE 里用 SELECT 的别名」：按逻辑顺序 WHERE 排在 SELECT 之前，别名那时还不存在（SQLite 宽容地放行了，换个库就报错）。但 WHERE 永远看得见一张已经算完的表 —— 把带别名的那一层塞进 FROM，外层再去 WHERE 那个别名，顺序问题就消失了。这就是「包一层子查询」这个套路的全部原理。

三种一起跑一遍（conv 是一张会话表：7 行，其中 4 行的 updated_at 都是 100）：

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE conv (id INTEGER PRIMARY KEY, uid INT, updated_at INT)")
db.executemany("INSERT INTO conv VALUES (?,?,?)",
               [(1,7,100),(2,7,100),(3,7,100),(4,7,100),(5,7,99),(6,7,98),(7,8,100)])

print("① 标量（一个值）:", db.execute(
    "SELECT (SELECT MAX(updated_at) FROM conv)").fetchone())
print("② 行（一行多列）:", db.execute(          # ⭐ 行子查询：左右都是「一整行」
    "SELECT * FROM conv WHERE (updated_at, id) = "
    "(SELECT updated_at, id FROM conv ORDER BY updated_at DESC, id DESC LIMIT 1)").fetchall())
print("③ 表（多行）    :", db.execute(
    "SELECT COUNT(*) FROM (SELECT id FROM conv WHERE uid = 7)").fetchone())

实测输出：

操作步骤

标量（一个值）: (100,)
行（一行多列）: [(7, 8, 100)]
表（多行） : (6,)

⚠️ 形状对不上的时候，未必会报错。 第六节有三个具体的坑，其中一个 SQLite 一声不吭地给你一个错答案。

🔁 二、相关 vs 非相关：判据是「单独拎出来能不能跑」

这是子查询的第二个分类维度，和形状正交，但很多人分不清：

非相关：子查询里没有任何一个东西来自外层。它是一段独立的查询，整条语句只算一次。
相关（correlated）：子查询里引用了外层的列。它依赖外层当前这一行，概念上每行跑一次。

⭐ 判据不用去数括号，一句话就够：把子查询单独复制出来能不能跑。 能跑就是非相关，报「no such column」就是相关。

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE conv (id INTEGER PRIMARY KEY, uid INT, updated_at INT)")
db.executemany("INSERT INTO conv VALUES (?,?,?)",
               [(1,7,100),(2,7,100),(3,7,100),(4,7,100),(5,7,99),(6,7,98),(7,8,100)])
db.execute("CREATE TABLE msg (conv_id INT, role TEXT, tokens INT)")
db.executemany("INSERT INTO msg VALUES (?,?,?)", [
    (1,"user",10),(1,"assistant",50),(1,"assistant",60),
    (2,"user",20),
    (5,"user",5),(5,"assistant",7),
    (7,"user",30),(7,"assistant",40),(7,"assistant",11),(7,"user",9),
])

print("非相关 :", db.execute(
    "SELECT id FROM conv WHERE updated_at = (SELECT MAX(updated_at) FROM conv) "
    "ORDER BY id").fetchall())
print("  单独拎出来跑 :", db.execute("SELECT MAX(updated_at) FROM conv").fetchone())

print("相关 :", db.execute(               # ⭐ 子查询里引用了外层的 c.id
    "SELECT c.id, (SELECT COUNT(*) FROM msg m WHERE m.conv_id = c.id) "
    "FROM conv c ORDER BY c.id").fetchall())
print("  单独拎出来跑 :", end=" ")
try:
    db.execute("SELECT COUNT(*) FROM msg m WHERE m.conv_id = c.id").fetchone()
except sqlite3.Error as e:
    print(type(e).__name__, "-", e)

print("改写成 JOIN 内连接 :", db.execute(
    "SELECT c.id, COUNT(*) FROM conv c JOIN msg m ON m.conv_id = c.id "
    "GROUP BY c.id ORDER BY c.id").fetchall())
print("改写成 JOIN 左连接 :", db.execute(
    "SELECT c.id, COUNT(m.conv_id) FROM conv c LEFT JOIN msg m ON m.conv_id = c.id "
    "GROUP BY c.id ORDER BY c.id").fetchall())

实测输出：

对照

非相关 : [(1,), (2,), (3,), (4,), (7,)]

单独拎出来跑 : (100,)

相关 : [(1, 3), (2, 1), (3, 0), (4, 0), (5, 2), (6, 0), (7, 4)]

单独拎出来跑 : OperationalError - no such column: c.id

改写成 JOIN 内连接 : [(1, 3), (2, 1), (5, 2), (7, 4)]

改写成 JOIN 左连接 : [(1, 3), (2, 1), (3, 0), (4, 0), (5, 2), (6, 0), (7, 4)]

⭐ 看最后三行：相关标量子查询给了 7 行（一条会话一行，没消息的写 0），内连接的 GROUP BY 只给了 4 行 —— 会话 3、4、6 一条消息都没有，内连接根本没把它们带进来。左连接才回到 7 行。

⭐ 这就是相关标量子查询最值钱的性质：它天然是「左连接」的。 外层有几行，结果就有几行，子查询查不到东西时给的是 NULL（COUNT 给 0），而不是让这一行消失。 ⚠️ 用 JOIN 改写它，第一件要问自己的事就是「该用 JOIN 还是 LEFT JOIN」—— 答错了，看板上那几个「零调用的会话」会整行不见，而且不报错。

⚠️ 「每行跑一次」是语义，不是性能承诺。 数据库大概率会把它改写成一次连接（第六节末尾有本机的执行计划）。先按语义选写法，慢了再回到 09 章看计划。

🎚️ 三、⭐ 行值：整行一起比，不是逐列比

《AI 全栈》06b 列表接口与分页里有一行 SQL，很多人抄过但没想过它凭什么成立：

WHERE (updated_at, id) < (?, ?)

⭐ (updated_at, id) 是一个「行值（row value）」：把几个列打包成一整行，然后整行和整行比大小。

它的定义就一句话：字典序（电话簿序）。先比第一列，分出胜负就结束；打平了才轮到第二列。手工展开等价于：

WHERE updated_at < ?  OR  (updated_at = ?  AND  id < ?)

⚠️⚠️ 它绝不等于 updated_at < ? AND id < ?。 这是最常见的误读，而且错得非常安静：

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE conv (id INTEGER PRIMARY KEY, uid INT, updated_at INT)")
db.executemany("INSERT INTO conv VALUES (?,?,?)",
               [(1,7,100),(2,7,100),(3,7,100),(4,7,100),(5,7,99),(6,7,98),(7,8,100)])

O = "ORDER BY updated_at DESC, id DESC"
rowval = [r[0] for r in db.execute(f"SELECT id FROM conv WHERE (updated_at, id) < (100, 3) {O}")]
andver = [r[0] for r in db.execute(f"SELECT id FROM conv WHERE updated_at < 100 AND id < 3 {O}")]
single = [r[0] for r in db.execute(f"SELECT id FROM conv WHERE updated_at < 100 {O}")]

print("行值 (updated_at, id) < (100, 3) ->", rowval)
print("逐列 updated_at < 100 AND id < 3 ->", andver)
print("单列 updated_at < 100            ->", single)
print("当作「下一页」各取 2 条 -> 行值:", rowval[:2], " 单列:", single[:2])

for a, b in [("(100,3)", "(100,5)"), ("(100,3)", "(99,999)"), ("(1,NULL)", "(1,5)"),
             ("(1,NULL)", "(2,5)")]:
    print(f"  {a} < {b} ->", db.execute(f"SELECT {a} < {b}").fetchone()[0])

实测输出：

关键信息

行值 (updated_at, id) < (100, 3) -> [2, 1, 5, 6]

逐列 updated_at < 100 AND id < 3 -> []

单列 updated_at < 100 -> [5, 6]

当作「下一页」各取 2 条 -> 行值: [2, 1] 单列: [5, 6]

(100,3) < (100,5) -> 1

(100,3) < (99,999) -> 0

(1,NULL) < (1,5) -> None

(1,NULL) < (2,5) -> 1

三行结果，三件事：

写法	命中	它实际在说什么
行值 `(updated_at, id) < (100, 3)`	4 行 `[2,1,5,6]`	⭐ 「在 `ORDER BY updated_at DESC, id DESC` 这个全序上，排在 `(100,3)` 后面的」
逐列 `updated_at < 100 AND id < 3`	0 行	「时间更早并且 id 更小」—— 两个毫不相干的条件求交集，💀 这个例子里交集是空的
单列 `updated_at < 100`	2 行 `[5,6]`	「跳过整个 `updated_at = 100` 的那一段」—— ⚠️ 那一段里没读完的 `id=2`、`id=1` 被整段跳过

⭐ 游标分页要的正是第一种。 ORDER BY updated_at DESC, id DESC 定义了一个全序，游标的任务是「从这个全序上的某一点接着往下取」；能表达「在这个全序上排在某一点之后」的只有行值比较，因为它用的正是同一套字典序。 ⚠️ (100,3) < (99,999) 算出 0 就是证据：第一列 100 > 99 已经分出胜负，第二列那个巨大的 999 一眼都没看。

⚠️ 行值里出现 NULL 也遵守同一条规则：(1,NULL) < (1,5) 是 None（第一列打平，胜负交给第二列，而第二列有 NULL）；但 (1,NULL) < (2,5) 是 1（第一列就分出了胜负，第二列根本没参与）。

行值不只能比大小，也能配 = 和 IN。一个立刻能用上的例子 —— 「每个用户最新的一条会话」：

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE conv (id INTEGER PRIMARY KEY, uid INT, updated_at INT)")
db.executemany("INSERT INTO conv VALUES (?,?,?)",
               [(1,7,100),(2,7,100),(3,7,100),(4,7,100),(5,7,99),(6,7,98),(7,8,100)])

print("用户数 :", db.execute("SELECT COUNT(DISTINCT uid) FROM conv").fetchone()[0])
bad = db.execute("SELECT id, uid, updated_at FROM conv c WHERE updated_at = "
                 "(SELECT MAX(updated_at) FROM conv c2 WHERE c2.uid = c.uid) "
                 "ORDER BY id").fetchall()
good = db.execute("SELECT id, uid, updated_at FROM conv c WHERE (updated_at, id) = "
                  "(SELECT updated_at, id FROM conv c2 WHERE c2.uid = c.uid "
                  " ORDER BY updated_at DESC, id DESC LIMIT 1) ORDER BY id").fetchall()
print("⚠️ 只比 MAX(updated_at) ->", len(bad), "行", bad)
print("✅ 比行值 (updated_at,id) ->", len(good), "行", good)

实测输出：

结果对照

用户数 : 2

⚠️ 只比 MAX(updated_at) -> 5 行 [(1, 7, 100), (2, 7, 100), (3, 7, 100), (4, 7, 100), (7, 8, 100)]

✅ 比行值 (updated_at,id) -> 2 行 [(4, 7, 100), (7, 8, 100)]

💀 两个用户，MAX 版本返回了 5 行。 因为用户 7 有 4 条会话的 updated_at 都是 100，= MAX(...) 把它们全部判为「最新」。这个 bug 在测试数据里几乎撞不上（时间戳很少撞车），一到线上批量回填就集中爆发。 ⭐ 修法和游标分页是同一件事：在排序键末尾补一个唯一列（通常是主键）当决胜，然后整行比。 （07 章会用 ROW_NUMBER() 再解一遍同一个问题 —— 那是更顺手的写法，但决胜列这条规矩一模一样。）

⚠️ 边界：AI 全栈 06b owns 复合游标怎么用（游标怎么编码成不透明串、翻页途中那条记录被删了怎么办、要配什么索引）；本节只 owns 它是什么。 🗓️ 未实跑：Postgres 和 MySQL 都支持行值比较的语法，但能不能用上复合索引各版本差别很大，上生产前请自己 EXPLAIN 一遍。

🛑 读到这里可以停 —— 前半章讲完了（约 38 分钟）。 后半章还有：IN / EXISTS / JOIN：同一个问题，三种写法 · 三选一里唯一写死的一格：「不在里面」不用 NOT IN · 剩下三个容易踩的回来的时候不用重读，直接从下一节接着看就行。

🚦 四、`IN` / `EXISTS` / `JOIN`：同一个问题，三种写法

问题：哪些会话里出现过 assistant 消息？

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE conv (id INTEGER PRIMARY KEY, uid INT, updated_at INT)")
db.executemany("INSERT INTO conv VALUES (?,?,?)",
               [(1,7,100),(2,7,100),(3,7,100),(4,7,100),(5,7,99),(6,7,98),(7,8,100)])
db.execute("CREATE TABLE msg (conv_id INT, role TEXT, tokens INT)")
db.executemany("INSERT INTO msg VALUES (?,?,?)", [
    (1,"user",10),(1,"assistant",50),(1,"assistant",60),
    (2,"user",20),
    (5,"user",5),(5,"assistant",7),
    (7,"user",30),(7,"assistant",40),(7,"assistant",11),(7,"user",9),
])

qs = {
 "IN      ": "SELECT c.id FROM conv c WHERE c.id IN "
             "(SELECT conv_id FROM msg WHERE role='assistant') ORDER BY c.id",
 "EXISTS  ": "SELECT c.id FROM conv c WHERE EXISTS "
             "(SELECT 1 FROM msg m WHERE m.conv_id=c.id AND m.role='assistant') ORDER BY c.id",
 "JOIN    ": "SELECT c.id FROM conv c JOIN msg m ON m.conv_id=c.id "
             "AND m.role='assistant' ORDER BY c.id",
 "JOIN+DIS": "SELECT DISTINCT c.id FROM conv c JOIN msg m ON m.conv_id=c.id "
             "AND m.role='assistant' ORDER BY c.id",
}
for name, q in qs.items():
    rows = [r[0] for r in db.execute(q)]
    print(f"{name} -> {len(rows)} 行 {rows}")

print("EXISTS 版 SUM :", db.execute(          # ⭐ 同一个问题接一步聚合
    "SELECT SUM(c.updated_at) FROM conv c WHERE EXISTS "
    "(SELECT 1 FROM msg m WHERE m.conv_id=c.id AND m.role='assistant')").fetchone()[0])
print("JOIN   版 SUM :", db.execute(
    "SELECT SUM(c.updated_at) FROM conv c JOIN msg m ON m.conv_id=c.id "
    "AND m.role='assistant'").fetchone()[0])

实测输出：

关键信息

IN -> 3 行 [1, 5, 7]

EXISTS -> 3 行 [1, 5, 7]

JOIN -> 5 行 [1, 1, 5, 7, 7]

JOIN+DIS -> 3 行 [1, 5, 7]

EXISTS 版 SUM : 299

JOIN 版 SUM : 499

⭐ IN 和 EXISTS 是 3 行，JOIN 是 5 行。 会话 1 和会话 7 各有 2 条 assistant 消息，JOIN 就把它们各带出来 2 遍 —— 这正是 02 章那个扇出，只不过换了张表。接一步 SUM 立刻变成 299 对 499：没有报错，账就这么错了。

⭐ IN 和 EXISTS 做的是「半连接（semi-join）」：只问右表有没有匹配的行，不把右表的行带进来。 JOIN 做的是「配对」：每一个匹配都产出一行。 ⚠️ 差别不在语法风格，在结果的行数，而行数错了下一步的聚合就全错。

三选一的判据表（按「右表的列你要不要」往下读）：

你想要的	用哪个	为什么
只想按右表筛选，右表的列一个都不要	⭐ `EXISTS`	语义就是「有没有」，不带行进来，结构上不可能扇出
判据是一小撮字面值或一个小的单列结果集	`IN`	最短最好读。⚠️ 但「不在里面」要用 `NOT EXISTS`，见第五节
右表的列要用（消息内容、标签名）	`JOIN`	只有它把右表的列带进结果。⚠️ 那就必须自己处理扇出
右表的聚合值要用（消息数、最后时间）	先聚合再连	先把右表压成「一个键一行」，扇出在聚合那一步就消掉了
每一行都要一个附加的小值，而且没匹配也要保留这一行	相关标量子查询	它天然是左连接语义（第二节）

⚠️ 别拿「IN 慢 EXISTS 快」这种口诀选写法。 那句话来自二十年前某些数据库的实现，今天不成立。给上面 IN 版和 EXISTS 版各加一句 EXPLAIN QUERY PLAN，本机（SQLite 3.50.4）给的计划完全不同（下面只留了操作名）：

算一算

IN : SEARCH c USING INTEGER PRIMARY KEY (rowid=?) / LIST SUBQUERY 1 / SCAN msg / CREATE BLOOM FILTER

EXISTS : SCAN c / CORRELATED SCALAR SUBQUERY 1 / SCAN m

⭐ IN 版反而先扫小表、建了个布隆过滤器再去主键上定位。结论：按语义选写法，性能问题交给 09 章去看计划。

💀 五、三选一里唯一写死的一格：「不在里面」不用 `NOT IN`

⚠️ 先划界。 04 章 owns 这个坑的全部机理 —— 三值逻辑的真值表、UNKNOWN 在 AND / OR 下怎么传播、以及四种反连接写法语义上的分叉（它实测出「NOT IN + 内层过滤」那种会比 NOT EXISTS 少一行，那是业务口径不同，不是 bug）。 本节只 owns 一件事：为什么上面那张三选一表里，「不在里面」那一格是写死的、没有权衡余地。

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE conv (id INTEGER PRIMARY KEY, uid INT, updated_at INT)")
db.executemany("INSERT INTO conv VALUES (?,?,?)",
               [(1,7,100),(2,7,100),(3,7,100),(4,7,100),(5,7,99),(6,7,98),(7,8,100)])
db.execute("CREATE TABLE blk (uid INT)")
db.executemany("INSERT INTO blk VALUES (?)", [(8,), (None,)])   # ⚠️ 黑名单里混了一行 NULL

print("问：哪些 uid 不在黑名单里？（正确答案是 7）")
print("  NOT IN                ->", db.execute(
    "SELECT DISTINCT uid FROM conv WHERE uid NOT IN (SELECT uid FROM blk)").fetchall())
print("  NOT EXISTS            ->", db.execute(
    "SELECT DISTINCT uid FROM conv c WHERE NOT EXISTS "
    "(SELECT 1 FROM blk b WHERE b.uid = c.uid)").fetchall())
print("  LEFT JOIN ... IS NULL ->", db.execute(
    "SELECT DISTINCT c.uid FROM conv c LEFT JOIN blk b ON b.uid = c.uid "
    "WHERE b.uid IS NULL").fetchall())
print("  NOT IN + 先滤掉 NULL  ->", db.execute(
    "SELECT DISTINCT uid FROM conv "
    "WHERE uid NOT IN (SELECT uid FROM blk WHERE uid IS NOT NULL)").fetchall())

for expr in ["7 NOT IN (8)", "7 NOT IN (8, NULL)", "8 NOT IN (8, NULL)", "7 <> NULL"]:
    print(f"  {expr:<20} ->", db.execute(f"SELECT {expr}").fetchone()[0])

实测输出：

关键信息

问：哪些 uid 不在黑名单里？（正确答案是 7）

NOT IN -> []

NOT EXISTS -> [(7,)]

LEFT JOIN · IS NULL -> [(7,)]

NOT IN + 先滤掉 NULL -> [(7,)]

7 NOT IN (8) -> 1

7 NOT IN (8, NULL) -> None

8 NOT IN (8, NULL) -> 0

7 <> NULL -> None

💀 NOT IN 返回了空集，一行都没有，而且不报错。 黑名单表里多了一行 NULL（谁都想不到要去防的一行），整条业务规则就静默失效了。

为什么：x NOT IN (a, b) 展开就是 x <> a AND x <> b。而 7 <> NULL 不是真也不是假，是 UNKNOWN（上面实测的 None）。真 AND UNKNOWN = UNKNOWN，WHERE 只放行确定为真的行 —— 于是所有没被匹配上的行全被 UNKNOWN 吃掉了。

⚠️ 注意 8 NOT IN (8, NULL) 算出的是确定的 0（因为 8 <> 8 是确定的假，假 AND 任何 都是假）。

⭐ 所以 NOT IN 只在「确实匹配上」时给确定答案，「没匹配上」时给 UNKNOWN —— 恰好把你想要的那些行全部丢掉。

⚠️ UNKNOWN 在 AND / OR 下的完整传播规则、IS NULL 为什么不能写成 = NULL、以及上面后三行为什么各不相同，全在 04 章第二节。本节只借它这一个结论。

回到子查询的视角，这个坑真正的意义是：它把「三选一」变成了「二选一」。IN 和 EXISTS 在肯定方向上确实可以随便挑（上一节实测两者都是 3 行 [1, 5, 7]），但一加上 NOT，IN 那一支就带上了一个不报错的失效模式，而 EXISTS 那一支没有 —— 因为 EXISTS 问的是「有没有配上的行」，b.uid = c.uid 判成 UNKNOWN 就是没配上，是个明确的否，不往外传染。

⭐ 判据可以背下来：只要是「不在某个集合里」，一律写 NOT EXISTS。 不用先去查那一列有没有 NULL —— 今天没有不代表明天没有，而这个 bug 不报错、不告警，只是安静地把结果变成空集。

⚠️ 上面四种写法在这份数据里答案一致（后三种都是 [(7,)]），是因为 conv.uid 这一列本身没有 NULL。当外层那一列自己也可能是 NULL 时它们会分叉 —— 那个分叉是业务口径的选择，04 章第四节实测并解释了它。

🛑 读到这里可以停 —— 已经读了约 64 分钟。 最后一段还有（约 32 分钟）：剩下三个容易踩的 · 检查点与走神救援回来的时候不用重读，直接从下一节接着看就行。

🧯 六、剩下三个容易踩的

① 标量位置塞进多行子查询，SQLite 不报错

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE conv (id INTEGER PRIMARY KEY, uid INT, updated_at INT)")
db.executemany("INSERT INTO conv VALUES (?,?,?)",
               [(1,7,100),(2,7,100),(3,7,100),(4,7,100),(5,7,99),(6,7,98),(7,8,100)])

print("多【行】的子查询塞进标量位置 :", end=" ")
print(db.execute("SELECT (SELECT id FROM conv ORDER BY id)").fetchone(), "← 只取了第一行")
print("多【列】的子查询塞进标量位置 :", end=" ")
try:
    db.execute("SELECT (SELECT id, uid FROM conv LIMIT 1)").fetchone()
except sqlite3.Error as e:
    print(type(e).__name__, "-", e)
print("FROM 子查询不给别名 :", db.execute("SELECT COUNT(*) FROM (SELECT id FROM conv)").fetchone())

实测输出：

要点

多【行】的子查询塞进标量位置 : (1,) ← 只取了第一行

多【列】的子查询塞进标量位置 : OperationalError - sub-select returns 2 columns - expected 1

FROM 子查询不给别名 : (7,)

💀 列数不对会报错，行数不对不会。 SQLite 悄悄取第一行 —— 而「第一行」在没有 ORDER BY 时是不做承诺的（01 章讲过这件事）。

⭐ 所以：写在标量位置的子查询，得由你自己保证它最多一行 —— 要么是聚合（MAX / COUNT），要么带明确的 ORDER BY + LIMIT 1。

🗓️ 未实跑：Postgres 在这种情况下会直接报「more than one row returned by a subquery」，MySQL 报 Subquery returns more than 1 row —— 换库时同一条 SQL 会从「悄悄错」变成「直接崩」。

② `FROM` 子查询的别名

SQLite 不要求（上面 FROM (SELECT id FROM conv) 跑通了），🗓️ 但 Postgres 和 MySQL 要求（未实跑）。⭐ 一律加别名 AS t，零成本，还让外层引用它的列时有名字可用。

③ `EXISTS` 的 `SELECT` 列表根本不求值

SELECT 1 还是 SELECT *，很多人纠结过。实测：连一个单独跑必定报错的表达式塞进去都没事。

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE msg (conv_id INT, role TEXT)")
db.executemany("INSERT INTO msg VALUES (?,?)",
               [(1,"user"),(1,"assistant"),(5,"assistant"),(7,"assistant")])

print("单独跑 :", end=" ")
try:
    db.execute("SELECT abs(-9223372036854775808)").fetchone()
except sqlite3.Error as e:
    print(type(e).__name__, "-", e)
for sel in ["1", "NULL", "*", "abs(-9223372036854775808)"]:
    n = db.execute("SELECT COUNT(*) FROM msg x WHERE EXISTS "
                   f"(SELECT {sel} FROM msg m WHERE m.conv_id = x.conv_id "
                   "AND m.role = 'assistant')").fetchone()[0]
    print(f"  EXISTS (SELECT {sel:<26}) -> {n}")

实测输出：

关键信息

单独跑 : OperationalError - integer overflow

EXISTS (SELECT 1 ) -> 4

EXISTS (SELECT NULL ) -> 4

EXISTS (SELECT * ) -> 4

EXISTS (SELECT abs(-9223372036854775808) ) -> 4

⭐ 那个溢出表达式单独跑必报错，放进 EXISTS 里四次都安然无恙 —— 证明 SELECT 列表压根没被求值，EXISTS 只关心「有没有行」。写 SELECT 1 是习惯，不是优化。

🔗 这一章连到哪里

相关的地方	为什么
02-JOIN的几种语义.html	⭐ 成对的另一半：那一章诊断扇出（`SUM` 从 150 变 250），这一章给药方。它第三节把 `EXISTS` 列为首选修法并写着「在 05 章展开」，本章第四节就是那个展开
04-NULL与三值逻辑.html	⭐ 上一节，也是欠着本章的那一半：它第四节给出「反连接一律写 `NOT EXISTS`」并写着「`IN` / `EXISTS` / `JOIN` 的取舍那一章接着讲」—— 本章第四节就是那个取舍。反过来，`NOT IN` 塌成空集的机理（三值逻辑、四种反连接写法的语义分叉）归它，本章第五节只借一个结论
AI全栈 06b 列表接口与分页	⭐ 那一章 owns 复合游标怎么用（编码成不透明串、翻页途中记录被删、配什么索引），本章第三节 owns 它是什么 —— 一个行值，比的是字典序
07-窗口函数.html	下一节的下一节。「每个用户最新的一条」用 `ROW_NUMBER()` 写更顺手，⭐ 但排序键末尾要补唯一列这条规矩一字不变 —— 那一章第 6 题就是这件事
06-CTE与递归查询.html	下一节。`WITH` 是子查询的可读形态：本章的语义在 CTE 里一个字都没变，只是从括号里挪到了上面
09-计划里的连接与索引失效.html	本章反复说「按语义选写法，性能交给计划」—— 那一章就是怎么看计划、以及哪些写法会毁掉索引
01-一条查询是怎么跑的.html	⭐ 它第三节那条判据「写了 `LIMIT` 就必须写 `ORDER BY`，且排序键要能唯一定序」写着「这正是 05 章那个复合游标的来历」—— 本章第三节就是那个来历。标量子查询「悄悄取第一行」之所以危险，也是同一条：不带 `ORDER BY` 时第一行是谁，SQL 不做承诺

✅ 检查点

子查询的三种形状各是什么？决定该用哪一种的是子查询自己，还是它出现的位置？
怎么一眼分辨相关子查询和非相关子查询？说出那条不用数括号的判据。
相关标量子查询和 JOIN + GROUP BY 改写，实测行数为什么不一样（7 行 vs 4 行）？该怎么补？
(updated_at, id) < (100, 3) 的定义是什么？为什么它不等于 updated_at < 100 AND id < 3？实测两者各命中几行？
单列游标 updated_at < 100 和行值游标各取 2 条，实测分别是哪两条？漏掉的是谁、为什么？
「每个用户最新的一条会话」只比 MAX(updated_at)，两个用户为什么实测返回了 5 行？修法是什么？
同一个存在性问题，IN / EXISTS / JOIN 实测各返回几行？接一步 SUM 之后两个数各是多少？
三选一的判据是什么？右表的聚合值要用时该怎么写？
NOT IN 遇到含 NULL 的子查询，实测返回什么？把 x NOT IN (a, b) 展开成等价式子，说明它为什么塌。
为什么说这个坑「把三选一变成了二选一」？NOT EXISTS 凭什么不被传染？实测四种写法在这份数据里各返回什么，它们什么时候会分叉？
标量位置塞进一个多行子查询，SQLite 是什么反应？多列呢？这对写法有什么要求？
怎么证明 EXISTS 的 SELECT 列表根本没被求值？

👀 答案

标量（一行一列 = 一个值）、行（一行多列）、表（多行）。决定形状的是位置：SELECT 列表 / ORDER BY / 和一个列比大小的右边必须是标量；和行值比较的必须是行；FROM / IN / EXISTS 后面是表（IN 还要求只有一列）。
把子查询单独复制出来能不能跑：能跑 = 非相关（整条语句只算一次）；报 no such column（实测 no such column: c.id）= 相关（引用了外层的列，概念上每行跑一次）。
相关标量子查询给 7 行（会话 3、4、6 一条消息都没有，写成 0），内连接的 GROUP BY 只给 4 行 —— 内连接把没有匹配的左行整行丢掉了。补法是改成 LEFT JOIN 且用 COUNT(m.conv_id) 而不是 COUNT(*)，实测回到 7 行。相关标量子查询天然是左连接语义。
定义是字典序：先比第一列，分出胜负就结束，打平了才比第二列；等价展开是 updated_at < 100 OR (updated_at = 100 AND id < 3)。它不是两个条件求交集。实测行值命中 4 行 [2,1,5,6]，逐列 AND 命中 0 行。⚠️ (100,3) < (99,999) 算出 0，因为第一列已分胜负，第二列没看。
行值游标 → [2, 1]；单列游标 → [5, 6]。⚠️ 漏掉的是 id=2、id=1 —— 它们的 updated_at 也是 100，被 updated_at < 100 整段跳过了。只有行值比较和 ORDER BY updated_at DESC, id DESC 这个全序一一对应。
因为用户 7 有 4 条会话的 updated_at 都是 100，= MAX(...) 把它们全判成「最新」，2 个用户实测出 5 行。修法：排序键末尾补一个唯一列（主键）当决胜，然后整行比 —— (updated_at, id) = (SELECT updated_at, id … ORDER BY updated_at DESC, id DESC LIMIT 1)，实测回到 2 行。
IN 3 行、EXISTS 3 行、JOIN 5 行（会话 1 和 7 各有 2 条 assistant 消息，各被带出 2 遍）、JOIN + DISTINCT 回到 3 行。接 SUM(updated_at)：EXISTS 版 299，JOIN 版 499。IN/EXISTS 是半连接（只问有没有），JOIN 是配对（每个匹配产出一行）。
右表的列你到底要不要。不要 → EXISTS（结构上不可能扇出）；判据是一小撮字面值/单列小结果集 → IN；要右表的列 → JOIN（自己处理扇出）；要右表的聚合值 → 先聚合再连（扇出在聚合那一步就消掉了）；每行加个小值且没匹配也要保留该行 → 相关标量子查询。
实测返回 []（空集），且不报错。展开是 x <> a AND x <> b，而 7 <> NULL 是 UNKNOWN（实测 None），真 AND UNKNOWN = UNKNOWN，WHERE 只放行确定为真的行。⚠️ 对照：8 NOT IN (8, NULL) 是确定的 0 —— 所以 NOT IN 只在「确实匹配上」时给确定答案，没匹配上的那些正是你想要的行，全被 UNKNOWN 吃掉。
因为 IN 和 EXISTS 在肯定方向上确实可以随便挑（实测都是 3 行 [1,5,7]），但一加 NOT，IN 那一支就带上了一个不报错的失效模式，EXISTS 那一支没有 —— EXISTS 问的是「有没有配上的行」，b.uid = c.uid 判成 UNKNOWN 就是没配上，是个明确的否，不往外传染。实测：NOT IN → []，NOT EXISTS / LEFT JOIN … IS NULL / NOT IN + 内层过滤 → 都是 [(7,)]。⚠️ 这里一致是因为 conv.uid 本身没有 NULL；外层那一列自己也可能是 NULL 时它们会分叉，那是业务口径的选择，在 04 章第四节。
多行：⚠️ 不报错，悄悄取第一行（实测 (1,)）；多列：报 sub-select returns 2 columns - expected 1。要求是自己保证它最多一行 —— 要么是聚合，要么带明确的 ORDER BY + LIMIT 1（不带 ORDER BY 时「第一行是谁」SQL 不做承诺）。🗓️ Postgres / MySQL 在这种情况下会直接报错（未实跑）。
把一个单独跑必定报错的表达式塞进去：abs(-9223372036854775808) 单独跑报 integer overflow，放进 EXISTS 的 SELECT 列表里和 1 / NULL / * 一样，实测都返回 4。证明列表压根没被求值，SELECT 1 是习惯不是优化。

🛑 可以停在这里

⚡ 走神救援

⭐ 子查询只有三种形状（一个值 / 一行 / 一张表），而决定用哪种的不是子查询自己，是它出现的位置。 第二个正交的维度是相关 vs 非相关——⭐ 判据不用数括号：把子查询单独复制出来能不能跑，报「no such column」的就是相关。

⭐ 相关标量子查询天然是左连接语义：它会保留没有匹配的那些行（补 0），而换成 JOIN + GROUP BY 会让那些行整行消失且不报错，得写 LEFT JOIN 才回得来。

⭐ 本章的核心是行值：把几列打包成一整行按字典序比——先比第一列，分出胜负就结束，打平了才比第二列。⚠️⚠️ 它绝不等于 a < ? AND b < ?：实测行值命中好几行，而逐列 AND 一行都没有。这就是复合游标分页成立的全部理由——排序定义了一个全序，只有行值比较和它一一对应；用单列游标会把同一秒里没读完的记录整段跳过。

同一条规矩也治「每个用户最新的一条」：只比 MAX(时间) 会在时间戳撞车时返回多行，⭐ 补上唯一列决胜才回到一行。

🚦 IN / EXISTS / JOIN 三选一，判据只有一句：右表的列你到底要不要。 不要就 EXISTS（半连接，结构上不可能扇出），要列就 JOIN。⚠️ 实测同一个存在性问题，JOIN 会把匹配多次的行各带出好几遍，接一步 SUM 结果就完全不同。

💀 唯一一条硬规矩：「不在某个集合里」一律写 NOT EXISTS——⭐ 这个坑把三选一变成了二选一，因为 NOT 只毒化 IN 那一支。

下一节 👉 06-CTE与递归查询.md

打卡记录保存在你的浏览器里，首页能看到总进度

05 · 子查询的三种形状，和 IN / EXISTS / JOIN 选哪个

🎯 一句话

🧩 一、三种形状：按「它返回什么」分类

🔁 二、相关 vs 非相关：判据是「单独拎出来能不能跑」

🎚️ 三、⭐ 行值：整行一起比，不是逐列比

🚦 四、IN / EXISTS / JOIN：同一个问题，三种写法

💀 五、三选一里唯一写死的一格：「不在里面」不用 NOT IN

🧯 六、剩下三个容易踩的

① 标量位置塞进多行子查询，SQLite 不报错

② FROM 子查询的别名

③ EXISTS 的 SELECT 列表根本不求值

🔗 这一章连到哪里

✅ 检查点

🛑 可以停在这里

🚦 四、`IN` / `EXISTS` / `JOIN`：同一个问题，三种写法

💀 五、三选一里唯一写死的一格：「不在里面」不用 `NOT IN`

② `FROM` 子查询的别名

③ `EXISTS` 的 `SELECT` 列表根本不求值