🏠 总目录📚 本教程 05 · 子查询的三种形状 ← →
📑 本页目录(点开跳转)

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
ORDER BY … LIMIT 1)
表子查询
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())

实测输出:

操作步骤

  1. 标量(一个值): (100,)
  2. 行(一行多列): [(7, 8, 100)]
  3. 表(多行) : (6,)

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

🔁 二、相关与非相关:检查是否引用外层列

如果每条会话都要附上自己的消息数,内层查询需要知道外层当前处理的是哪个会话。这就是判断相关性的线索。

跟一遍会话 3 的消息计数

  1. 从外层会话表读出 c.id = 3。
  2. 在内层消息表找 m.conv_id = 3。本例没有匹配。
  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) -> [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 将两列都按降序排列。两列的值都已知,末尾的主键 id 唯一,所以每条会话有确定的位置。行值 < 和它的 OR 展开都能取到游标之后的行。

例如 (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)

实测输出:

结果对照

用户数 : 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(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 -> 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 各有两条匹配的消息,因此在连接结果中各出现两次。

把更新时间相加只是为了看清重复计入。存在性筛选得到 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])

实测输出:

关键信息

问:哪些 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 的空集来自两种淘汰原因。UID 8 命中黑名单,条件为假。UID 7 与黑名单的缺值比较后,条件未知。右侧滤掉缺值后,NOT IN 对 UID 7 为真。

外侧 UID 缺失时,比较查询结果

把例子缩小成三条会话。会话 1 的 UID 是 7,右表没有 7。会话 2 的 UID 是 8,右表有一条备注缺失的记录。会话 3 的 UID 还没录入。右表的 id 是本例中有值的记录编号,note 是可以缺失的备注。

左表 conv · 会话
iduid
17
28
3NULL
右表 blk · 黑名单
iduidnote
108NULL
11NULL待补 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 也通过。

先定报表口径,再选写法

上节在相同匹配条件的肯定式 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 章讲过这件事)。

先确定标量子查询最多返回一行。

如果最终没有一行,标量子查询的值为 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}")

实测输出:

关键信息

单独跑 : OperationalError - integer overflow
EXISTS (SELECT 1 ) -> 4
EXISTS (SELECT NULL ) -> 4
EXISTS (SELECT * ) -> 4
EXISTS (SELECT abs(-9223372036854775808) ) -> 4

⭐ 上面四种原普通查询都返回 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 不做承诺

✅ 检查点

  1. 子查询的三种形状各是什么?决定该用哪一种的是子查询自己,还是它出现的位置?
  2. 怎么从列的来源分辨相关子查询和非相关子查询?用 m.conv_id = c.id 解释。
  3. 相关标量子查询和 JOIN + GROUP BY 改写,实测行数为什么不一样(7 行 vs 4 行)?该怎么补?
  4. (updated_at, id) < (100, 3) 的定义是什么?为什么它不等于 updated_at < 100 AND id < 3?实测两者各命中几行?
  5. 单列游标 updated_at < 100 和行值游标各取 2 条,实测分别是哪两条?漏掉的是谁、为什么?
  6. 「每个用户最新的一条会话」只比 MAX(updated_at),两个用户为什么实测返回了 5 行?修法是什么?
  7. 同一个存在性问题,IN / EXISTS / JOIN 实测各返回几行?接一步 SUM 之后两个数各是多少?
  8. 三选一的判据是什么?右表的聚合值要用时该怎么写?
  9. 逐项比较,算出 7 NOT IN (8)、7 NOT IN (8, NULL) 和 8 NOT IN (8, NULL) 的真值。哪些能通过 WHERE?如果右侧子查询返回零行,外侧 UID 缺失的会话会留下吗?
  10. 三行小表中,NOT EXISTS 为什么保留会话 3?报表只统计 UID 已知的会话时,怎样改写?如果左连接改查 b.note IS NULL,会多留哪一行?
  11. 标量位置塞进一个多行子查询,SQLite 是什么反应?多列呢?这对写法有什么要求?
  12. 原普通 EXISTS 查询怎样说明溢出投影未求值?为什么加入 INTERSECT 后不能照搬这个结论?
👀 答案
  1. 标量用于取一个值,零行给 NULL。行用于取一组列值。表可以提供多行多列。对 IN 的右侧子查询,列数与左侧匹配。
  2. 如果内层引用了外层的列,就是相关子查询。这里 m.conv_id 来自内层消息表,c.id 来自外层会话表。
  3. 在 SELECT 列表中为每条会话计算消息数,本例保留 7 行,会话 3、4、6 的计数是 0。内连接只返回 4 行。用 LEFT JOIN 保留未匹配会话,再用 COUNT(m.conv_id) 排除补出的空行,得到 7 行。
  4. 定义是字典序:先比第一列,分出胜负就结束,打平了才比第二列;等价展开是 updated_at < 100 OR (updated_at = 100 AND id < 3)。它不是两个条件求交集。实测行值命中 4 行 [2,1,5,6],逐列 AND 命中 0 行。⚠️ (100,3) < (99,999) 算出 0,因为第一列已分胜负,第二列没看。
  5. 行值游标 → [2, 1]。单列游标 → [5, 6]。会话 2 和 1 也在 100 秒,被单列条件跳过。本例用两个非空降序键、唯一编号和与之对应的行值条件,或等价的 OR 展开。
  6. 因为用户 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 行。
  7. IN 3 行、EXISTS 3 行、JOIN 5 行(会话 1 和 7 各有 2 条 assistant 消息,各被带出 2 遍)、JOIN + DISTINCT 回到 3 行。接 SUM(updated_at):EXISTS 版 299,JOIN 版 499。IN/EXISTS 是半连接(只问有没有),JOIN 是配对(每个匹配产出一行)。
  8. 问名单成员关系用 IN,问有无匹配用 EXISTS,要配对行的列用 JOIN。要右侧的聚合值时,可先按连接键聚合,再做连接。只需为每条会话附一个计数时,可以在 SELECT 列表中用相关标量子查询。
  9. 7 <> 8 为真,所以第一式通过。第二式多了 7 <> NULL,真 AND 未知得到未知。第三式有 8 <> 8 这个假,假 AND 未知得到假。后两式都无法通过。右侧子查询返回零行时,NOT IN 对所有外行都为真,包括 UID 缺失的会话。
  10. 会话 3 与右表两个 UID 的普通等号比较都未知,子查询找不到真匹配,所以 NOT EXISTS 为真。加上外层 c.uid IS NOT NULL 后,只留下会话 1。用 b.note IS NULL 会多留会话 2,因为它匹配的右行 10 恰好缺备注。检查 b.id IS NULL 可以识别左连接补出的右侧空行。
  11. SQLite 在标量位置遇到多行会取第一行,多列会报错,零行得到 NULL。先检查聚合是否分组,或用含唯一决胜键的 ORDER BY 和 LIMIT 1 明确选哪一行。PostgreSQL 和 MySQL 对标量位置的多行结果会报错。
  12. 原普通查询把 abs(-9223372036854775808) 放进 EXISTS 的 SELECT 列表,与 1 / NULL / * 一样返回 4,说明本例没有求值这个溢出投影;单独运行它仍报 integer overflow。加入 INTERSECT 的子查询可能要用投影值确定结果行,内存反例会报同一溢出错误。EXISTS 检查有没有行,不是子查询内部永不求值的保证;不要依赖表达式副作用。

本节回顾

本章比较了查询结果。想把多层查询摊开阅读,可以继续学 第 06 章 CTE 与递归查询。查询慢时,回到 第 09 章读执行计划。

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