📑 本页目录(点开跳转)
05 · 子查询的三种形状,和 IN / EXISTS / JOIN 选哪个
实验记录怎么读
本页原有耗时、内存、报错和环境值保留为原文记录;本轮内容审校未逐项复跑这些实验。版本、硬件、数据和测量方法都会影响结果,示例中的倍数也不是保证。需要复现时先核对依赖版本,再在同一输入下比较。
⏱ 预计 100 分钟。
🎯 一句话
要筛出有消息的会话,又要让每条会话只出现一次,可以先问数据库「这条会话有匹配的消息吗?」。第 02 章中,直接连接重复匹配曾把 SUM 从 150 放大到 250。复习连接扇出
子查询可以提供一个值、一行或一张表。先看查询位置需要什么,再决定是用 IN 查名单、用 EXISTS 查匹配,还是用 JOIN 取配对行。
🧩 一、三种形状:按「它返回什么」分类
子查询(subquery)是写在另一条查询里的查询。外层可以使用它提供的一个值、一行或一张表。下面按外层如何使用结果来分类。
| 形状 | 返回 | 长什么样 |
|---|---|---|
| 标量子查询 scalar subquery |
一列,写成至多一行。 零行时得到 NULL。 |
(SELECT MAX(updated_at) FROM conv) |
| 行子查询 row subquery |
取一组列值。 例如会话的时间和编号。 |
(SELECT updated_at, id FROM conv |
| 表子查询 table subquery |
零行或多行,可以包含多列。 | (SELECT id FROM conv WHERE uid = 7) |
按查询位置判断子查询形状
SELECT列表、ORDER BY或与单值比较- 在这里用标量子查询提供一个值。
- 和行值比较,如
(a, b) = (…) - 子查询提供一组列值。两侧的列数要相同,顺序要对应。
FROM或EXISTS后面FROM使用子查询提供的行和列。EXISTS检查子查询是否至少返回一行。IN/NOT IN的右侧子查询- 返回候选行,列数与左侧匹配。左侧是单值用一列,左侧是行值按位置对应。
试两个最小查询。 在 SQLite 中,SELECT (SELECT 1 WHERE 0) 得到 NULL,因为括号里没有一行能通过 WHERE 0。SELECT (1, 2) IN (SELECT 1, 2) 得到 1,因为两个位置都相等。SQLite 子查询规则 · 行值 IN
回忆 第 01 章。在常见的 SQL 写法中,同层 WHERE 先筛行,SELECT 才给计算结果命名。可以把计算放进 FROM 子查询,这样计算结果的名字就是外层查询可用的列名。
三种一起跑一遍。会话表 conv 有 7 行,其中 5 行的 updated_at 为 100。这组演示把时间写成整数秒,id 是会话编号,uid 是用户编号。
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 一声不吭地给你一个错答案。
🔁 二、相关与非相关:检查是否引用外层列
如果每条会话都要附上自己的消息数,内层查询需要知道外层当前处理的是哪个会话。这就是判断相关性的线索。
- 非相关子查询(uncorrelated subquery) 没有引用外层查询的列。例如对整张会话表求最大更新时间,每条外行看到的是同一个全表最大值。
- 相关子查询(correlated subquery) 引用了外层查询的列。例如消息计数里的
m.conv_id = c.id使用外层会话表别名c的编号。
跟一遍会话 3 的消息计数
- 从外层会话表读出
c.id = 3。 - 在内层消息表找
m.conv_id = 3。本例没有匹配。 COUNT(*)得到 0,外层这条会话输出(3, 0)。
单独运行时出现 no such column: c.id,可以回去检查 c 在哪一层定义。列名拼错也会报 no such column,例如独立查询 SELECT MAX(typo) FROM conv。
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 行。
本例把计数放在外层的
SELECT列表中,给每条已经选中的会话附一个值。改写成左连接时,用COUNT(m.conv_id)数匹配,左连接补出的空行才不会被算成一条消息。
位置变了,作用也会变。把相关计数放进 WHERE 并要求计数大于 0,外层就只会留下有消息的会话。SQLite 按需要取得相关子查询的结果,具体执行方式由查询计划决定。第四节的计划对照 · SQLite 相关子查询
🎚️ 三、⭐ 行值:整行一起比,不是逐列比
一页读到更新时间 100 秒、会话编号 3,下一页要从这条记录之后继续。用这两个值记录翻页位置,就是复合游标(composite cursor)。列表接口与分页
WHERE (updated_at, id) < (?, ?)
(updated_at, id) 是一个行值(row value)。它把两个值按顺序放在一起。上面两个 ? 是参数占位符,执行时依次绑定时间 100 和编号 3。
字典序(lexicographic order)先比较第一列。第一列相同,才比较第二列。对本例的整数键,展开成下面的 OR 条件也能得到同样的行。三个占位符依次绑定 100, 100, 3。
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) |
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 将两列都按降序排列。两列的值都已知,末尾的主键 id 唯一,所以每条会话有确定的位置。行值 < 和它的 OR 展开都能取到游标之后的行。
- 排序方向改变。 若时间降序、编号升序,游标仍是
(100, 3),同为 100 秒时,(100, 4)在后,(100, 2)在前。此时用updated_at < 100 OR (updated_at = 100 AND id > 3),第二个比较符跟随编号的方向。 - 排序键可能缺值。 普通比较可能得到未知。分页前要明确是否纳入缺值记录,以及它们排在哪里。若要纳入,翻页条件也要覆盖缺值所在的排序位置。
例如 (1, NULL) < (1, 5) 得到未知,第一列相同,第二列有缺值。(1, NULL) < (2, 5) 得到真,第一列已确定结果。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("用户数 :", 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)
实测输出:
结果对照
只比较 MAX(updated_at) 时,UID 7 有四条会话都符合 100 秒,UID 8 有一条,因此共返回 5 行。
把 id 作为最后一个排序键,每个用户只选一条,再比较完整行值,结果是会话 4 和 7。第 07 章会用窗口函数解决同一问题。
游标编码、翻页途中记录被删和索引选择见 AI 全栈第 06b 章。PostgreSQL 和 MySQL 支持行值比较,能否使用具体索引要查看该引擎的实际查询计划。
🚦 四、IN / EXISTS / JOIN:同一个问题,三种写法
问题:哪些会话里出现过 assistant 消息?先预测会话 1 会出现几次,再运行下面三种写法。
半连接(semi-join)按有没有匹配来筛左表的行。这里 IN 查看会话编号是否在名单里,EXISTS 查看是否找到一条匹配的消息。JOIN 则为每一对匹配行产出结果。
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 和 EXISTS 各返回 3 行,JOIN 返回 5 行。会话 1 和 7 各有两条匹配的消息,因此在连接结果中各出现两次。
把更新时间相加只是为了看清重复计入。存在性筛选得到
100 + 99 + 100 = 299,连接后还多算两个 100,得到 499。
按查询需求选写法
- 只按右表是否有匹配来筛选
EXISTS。右侧找到一行就满足条件,右侧的多次匹配不会复制这条左行。- 检查一个值是否在单列名单里
IN。名单可以直接写出,也可以由子查询提供。排除名单时,先到第五节检查两侧缺值。- 取右表的消息内容或标签名
JOIN。可以取到配对行的列。一条左行匹配多条右行时,结果会增加行数。- 取右表的消息数或最后时间
- 先聚合再连接。先按连接键把右表汇总成每键一行,再用同一个键连接。
- 给每条外行附一个值,保留没有匹配的行
- 相关标量子查询。放在
SELECT列表中,本章的消息计数示例会给无匹配的会话附上 0。
EXPLAIN QUERY PLAN 显示 SQLite 打算怎样找行,例如扫描全表或用主键定位。它输出计划,实际耗时需要另测。下面保留 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
这个计划里的布隆过滤器(Bloom filter)能提前排除一些肯定不在候选集中的键。它给出「可能匹配」时,数据库仍要核实。不同版本可以为同样的查询选择不同计划。第 09 章会继续读查询计划。
五、反连接:先确定谁算未匹配
上一节问「哪些会话有匹配的消息」。现在要审查会话,找出没有匹配黑名单记录的会话。conv 是会话表,blk 是黑名单,uid 是用户编号。按条件找出左表中没有匹配右行的记录,叫反连接(anti-join)。如果会话的 UID 缺失,报表是否应把它列为「未查到匹配」?先算真值,再决定哪些行应保留。
先用 7 和 8 算一遍
回顾 04 章的三值逻辑。WHERE 只保留条件为真(TRUE)的行。普通等号或不等号与 NULL 比较会得到未知(UNKNOWN)。
用单列 UID 算一个例子。x 是当前用户编号,a、b 是名单里的两个候选值。已知值都是整数,缺值记为 NULL。
x NOT IN (a, b) 在这里可以展开成 x <> a AND x <> b。<> 表示不等于。两次比较都为真,整条条件才为真。
逐项比较,再决定能否通过 WHERE
7 NOT IN (8)7 <> 8为真,得到 TRUE,通过 WHERE。7 NOT IN (8, NULL)7 <> 8为真,7 <> NULL未知。真 AND 未知得到 UNKNOWN,不通过 WHERE。8 NOT IN (8, NULL)8 <> 8为假,8 <> NULL未知。假 AND 未知得到 FALSE,不通过 WHERE。NULL NOT IN (8)NULL <> 8得到 UNKNOWN,不通过 WHERE。
混合类型要按数据库的规则比较。SQLite 的列亲和性(column affinity)是列偏好的数据类型,例如整数列倾向把文本 '7' 转为整数。SQLite 的类型转换规则
找到一个确定相等的值,NOT IN 就为假。所有比较都确定不等,结果为真。没有确定相等的值、且至少一次比较未知,结果才是未知。右侧子查询返回零行时,没有任何候选值需要排除,NOT IN 为真,即使左值是 NULL。
下面的 SQLite 实验用原来的 7 条会话,左侧 UID 只有 7 和 8。黑名单右侧有 8 和 NULL。SELECT DISTINCT uid 让同一个 UID 只显示一次。本实验的报表只比较已知 UID。末尾的 SELECT 直接显示条件值,SQLite 的真、假分别显示为 1、0,Python 用 None 显示 SQL 的 NULL 结果。
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])
实测输出:
关键信息
这份数据里,原始 NOT IN 的空集来自两种淘汰原因。UID 8 命中黑名单,条件为假。UID 7 与黑名单的缺值比较后,条件未知。右侧滤掉缺值后,NOT IN 对 UID 7 为真。
外侧 UID 缺失时,比较查询结果
把例子缩小成三条会话。会话 1 的 UID 是 7,右表没有 7。会话 2 的 UID 是 8,右表有一条备注缺失的记录。会话 3 的 UID 还没录入。右表的 id 是本例中有值的记录编号,note 是可以缺失的备注。
| id | uid |
|---|---|
| 1 | 7 |
| 2 | 8 |
| 3 | NULL |
| id | uid | note |
|---|---|---|
| 10 | 8 | NULL |
| 11 | NULL | 待补 UID |
c 和 b 分别是两张表的别名。B 对当前会话找满足 b.uid = c.uid 的右行,SELECT 1 为每条匹配行返回一个占位值。会话 3 的 UID 与右表两个值比较都是未知,子查询返回零行,所以 NOT EXISTS 为真。E 多加外层条件 c.uid IS NOT NULL,会排除会话 3。
C 的 ON 使用同一个匹配条件。会话 2 真正配到右行 10,右侧 id 是 10,备注却是 NULL。会话 1 和 3 未匹配,左连接补出的右侧 id 才是 NULL。F 检查备注,会把已经匹配的会话 2 也留下。
下面的 Python 可以单独运行。它在内存中建好这两张表,用同一个 SELECT c.id FROM conv c 接上各组条件,按会话编号排序后打印编号列表。Python 的 None 在写入时对应 SQL 的 NULL。
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE conv (id INTEGER PRIMARY KEY, uid INTEGER)")
db.execute("CREATE TABLE blk (id INTEGER PRIMARY KEY, uid INTEGER, note TEXT)")
db.executemany("INSERT INTO conv VALUES (?, ?)", [(1, 7), (2, 8), (3, None)])
db.executemany("INSERT INTO blk VALUES (?, ?, ?)",
[(10, 8, None), (11, None, "待补 UID")])
queries = {
"A 原始 NOT IN": "WHERE c.uid NOT IN (SELECT uid FROM blk)",
"B NOT EXISTS": "WHERE NOT EXISTS "
"(SELECT 1 FROM blk b WHERE b.uid = c.uid)",
"C 左连接检查 id": "LEFT JOIN blk b ON b.uid = c.uid WHERE b.id IS NULL",
"D NOT IN 滤右侧": "WHERE c.uid NOT IN "
"(SELECT uid FROM blk WHERE uid IS NOT NULL)",
"E NOT EXISTS 滤外侧": "WHERE c.uid IS NOT NULL AND NOT EXISTS "
"(SELECT 1 FROM blk b WHERE b.uid = c.uid)",
"F 左连接误查 note": "LEFT JOIN blk b ON b.uid = c.uid WHERE b.note IS NULL",
"G NOT IN 空子查询": "WHERE c.uid NOT IN (SELECT uid FROM blk WHERE 1 = 0)",
}
for label, clause in queries.items():
ids = [row[0] for row in db.execute(
f"SELECT c.id FROM conv c {clause} ORDER BY c.id"
)]
print(label, ids)
db.close()
SQLite 实测 · 返回的会话编号
- A 原始
NOT IN []。会话 1 和 3 的条件未知,会话 2 的条件为假。- B
NOT EXISTS [1, 3]。这两条会话都找不到等号为真的右行。- C 左连接检查
b.id [1, 3]。这两条会话的右侧编号是左连接补出的NULL。- D
NOT IN滤右侧 [1]。右侧只剩 8,7 NOT IN (8)为真,NULL NOT IN (8)未知。- E
NOT EXISTS滤外侧 [1]。外层先要求 UID 已知,只留下没有匹配的会话 1。- F 左连接误查
b.note [1, 2, 3]。会话 2 匹配到的真实右行恰好没有备注,因此被误留。- G
NOT IN空子查询 [1, 2, 3]。WHERE 1 = 0使右侧没有候选值,外侧缺值的会话 3 也通过。
先定报表口径,再选写法
- 找不到满足匹配条件的右行,用
NOT EXISTS直接表达。使用普通等号时,左侧 UID 缺失的行也会留下。只统计 UID 已知的会话时,显式加c.uid IS NOT NULL。 - 排除一份已知的单列名单,可以用
NOT IN。如果口径是只比较已知 UID,就同时保证右侧名单无NULL、左侧 UID 有值。表约束可以提供保证,也可以像 D 那样滤右侧,再显式加左侧IS NOT NULL。左侧条件在右侧被滤空时依然有效。 - 已有左连接,要筛出没配到右行的记录,可以用
LEFT JOIN … WHERE b.id IS NULL。把决定「是否匹配」的右表条件写进ON,并检查匹配成功时必定有值的右列。原实验检查b.uid也成立,因为b.uid = c.uid为真时b.uid必有值。
上节在相同匹配条件的肯定式 WHERE 中,IN 和 EXISTS 都留下了会话 1、5、7。IN 的真值可以是未知,EXISTS 根据子查询有无行只给真或假。把两者分别取反时,未知仍是未知,原先的假则变为真。第五节的 A、B 正好显示这种差别。
可对照 04 章的反连接实验、SQLite 的 IN / NOT IN 真值表和 PostgreSQL 的 NOT IN 规则。
🧯 六、剩下三个容易踩的
① 标量位置塞进多行子查询,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 章讲过这件事)。
先确定标量子查询最多返回一行。
- 不分组的全表
COUNT(*)产生一个计数,空输入时是 0。MAX(...) GROUP BY uid按用户各产一行,两个用户可得到两行。 - 从多行中挑一行时,用含唯一决胜键的
ORDER BY配合LIMIT 1。
如果最终没有一行,标量子查询的值为 NULL。
🗓️ 未实跑:Postgres 在这种情况下会直接报「more than one row returned by a subquery」,MySQL 报 Subquery returns more than 1 row —— 换库时同一条 SQL 会从「悄悄错」变成「直接崩」。
② FROM 子查询的别名
SQLite 和 PostgreSQL 16 及以上版本允许省略 FROM 子查询的别名。PostgreSQL 15 和 MySQL 8.4 要求提供别名。写 AS t 能让外层用 t.列名 明确引用子查询的列。PostgreSQL 16 变更 · MySQL 8.4 派生表
③ 普通 EXISTS 例里,SELECT 投影未求值
投影(projection)是选定结果要输出哪些列或表达式。例如 SELECT 1 FROM msg 输出常量 1,SELECT * FROM msg 输出消息表的全部列。
下面把会溢出的表达式放进 EXISTS 里,观察它是否求值。abs 取绝对值,这个输入的绝对值比 SQLite 的最大整数还大 1,所以单独求值会报错。
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}")
实测输出:
关键信息
⭐ 上面四种原普通查询都返回 4,溢出投影没有触发。EXISTS 只问子查询是否返回行,不保证子查询内部不需要计算表达式。在这组查询里,写 SELECT 1 是表达意图的习惯,不是提速保证。
交集(INTERSECT)只留下两个查询都返回的行。SELECT 1 INTERSECT SELECT 1 留下一行,因为两边都有 1。
在 SQLite 中,EXISTS(SELECT abs(-9223372036854775808)) 得到 1。加入 INTERSECT SELECT 1 后报 integer overflow,因为要读取结果值才能判断有没有共同的行。SQLite EXISTS · 复合 SELECT
🔗 这一章连到哪里
| 相关的地方 | 为什么 |
|---|---|
| 02-JOIN的几种语义.html | ⭐ 成对的另一半:那一章诊断扇出(SUM 从 150 变 250),这一章给药方。它第三节把 EXISTS 列为首选修法并写着「在 05 章展开」,本章第四节就是那个展开 |
| 04-NULL与三值逻辑.html | 上一章第二节解释三值逻辑,第四节用用户和总部城市对照反连接写法,先问外侧缺值是否应保留。本章第四节比较存在性筛选和连接,第五节把缺值口径应用到会话与黑名单。 |
| 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 不做承诺 |
✅ 检查点
- 子查询的三种形状各是什么?决定该用哪一种的是子查询自己,还是它出现的位置?
- 怎么从列的来源分辨相关子查询和非相关子查询?用
m.conv_id = c.id解释。 - 相关标量子查询和
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之后两个数各是多少? - 三选一的判据是什么?右表的聚合值要用时该怎么写?
- 逐项比较,算出
7 NOT IN (8)、7 NOT IN (8, NULL)和8 NOT IN (8, NULL)的真值。哪些能通过WHERE?如果右侧子查询返回零行,外侧 UID 缺失的会话会留下吗? - 三行小表中,
NOT EXISTS为什么保留会话 3?报表只统计 UID 已知的会话时,怎样改写?如果左连接改查b.note IS NULL,会多留哪一行? - 标量位置塞进一个多行子查询,SQLite 是什么反应?多列呢?这对写法有什么要求?
- 原普通
EXISTS查询怎样说明溢出投影未求值?为什么加入INTERSECT后不能照搬这个结论?
👀 答案
- 标量用于取一个值,零行给
NULL。行用于取一组列值。表可以提供多行多列。对IN的右侧子查询,列数与左侧匹配。 - 如果内层引用了外层的列,就是相关子查询。这里
m.conv_id来自内层消息表,c.id来自外层会话表。 - 在
SELECT列表中为每条会话计算消息数,本例保留 7 行,会话 3、4、6 的计数是 0。内连接只返回 4 行。用LEFT JOIN保留未匹配会话,再用COUNT(m.conv_id)排除补出的空行,得到 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]。会话 2 和 1 也在 100 秒,被单列条件跳过。本例用两个非空降序键、唯一编号和与之对应的行值条件,或等价的OR展开。 - 因为用户 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 行。 IN3 行、EXISTS3 行、JOIN5 行(会话 1 和 7 各有 2 条 assistant 消息,各被带出 2 遍)、JOIN + DISTINCT回到 3 行。接SUM(updated_at):EXISTS版 299,JOIN版 499。IN/EXISTS是半连接(只问有没有),JOIN是配对(每个匹配产出一行)。- 问名单成员关系用
IN,问有无匹配用EXISTS,要配对行的列用JOIN。要右侧的聚合值时,可先按连接键聚合,再做连接。只需为每条会话附一个计数时,可以在SELECT列表中用相关标量子查询。 7 <> 8为真,所以第一式通过。第二式多了7 <> NULL,真 AND 未知得到未知。第三式有8 <> 8这个假,假 AND 未知得到假。后两式都无法通过。右侧子查询返回零行时,NOT IN对所有外行都为真,包括 UID 缺失的会话。- 会话 3 与右表两个 UID 的普通等号比较都未知,子查询找不到真匹配,所以
NOT EXISTS为真。加上外层c.uid IS NOT NULL后,只留下会话 1。用b.note IS NULL会多留会话 2,因为它匹配的右行 10 恰好缺备注。检查b.id IS NULL可以识别左连接补出的右侧空行。 - SQLite 在标量位置遇到多行会取第一行,多列会报错,零行得到
NULL。先检查聚合是否分组,或用含唯一决胜键的ORDER BY和LIMIT 1明确选哪一行。PostgreSQL 和 MySQL 对标量位置的多行结果会报错。 - 原普通查询把
abs(-9223372036854775808)放进EXISTS的SELECT列表,与1/NULL/*一样返回 4,说明本例没有求值这个溢出投影;单独运行它仍报integer overflow。加入INTERSECT的子查询可能要用投影值确定结果行,内存反例会报同一溢出错误。EXISTS检查有没有行,不是子查询内部永不求值的保证;不要依赖表达式副作用。
本节回顾
- 定形状。先看外层位置需要一个值、一组列还是多行。
- 查引用。沿列名找到它定义在哪一层。
- 按顺序翻页。让翻页条件与排序方向、缺值规则和唯一决胜键一致。
- 筛出未匹配行。先定缺值是否保留,再选
NOT EXISTS、单列NOT IN或左连接。
本章比较了查询结果。想把多层查询摊开阅读,可以继续学 第 06 章 CTE 与递归查询。查询慢时,回到 第 09 章读执行计划。