📑 本页目录(点开跳转)
02 · JOIN 的几种语义,和扇出这个隐形杀手
实验记录怎么读
本页原有耗时、内存、报错和环境值保留为原文记录;本轮内容审校未逐项复跑这些实验。版本、硬件、数据和测量方法都会影响结果,示例中的倍数也不是保证。需要复现时先核对依赖版本,再在同一输入下比较。
⏱ 78 分钟 | ⭐ 实测:JOIN 之后 SUM 从 150 变成 250——没报错,账就这么错了
🎯 一句话
JOIN 不是「把两张表拼在一起」,是「按条件做一次配对」——配对的结果行数,可能比任何一张原表都多。
一旦多出来,你后面所有的 SUM / COUNT / AVG 全部跟着放大,而数据库一个字都不会提醒你。
🚦 一、五种连接,一次跑完
三个用户、四张单,其中「阿花」一张单都没有,而 id=13 那张单挂在一个不存在的用户 99 上(孤儿行,真实系统里非常常见):
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE users (id INT, name TEXT)")
db.execute("CREATE TABLE orders(id INT, user_id INT, amount REAL)")
db.executemany("INSERT INTO users VALUES(?,?)",
[(1, "阿呆"), (2, "阿瓜"), (3, "阿花")]) # 阿花没下过单
db.executemany("INSERT INTO orders VALUES(?,?,?)",
[(10, 1, 100.0), (11, 1, 50.0), (12, 2, 30.0),
(13, 99, 7.0)]) # user_id=99 是孤儿单
def n(sql):
return len(db.execute(sql).fetchall())
base = "FROM users u {j} JOIN orders o ON o.user_id = u.id"
print("表里:", n("SELECT * FROM users"), "个用户,", n("SELECT * FROM orders"), "张单")
print("INNER JOIN :", n("SELECT * " + base.format(j="INNER")), "行")
print("LEFT JOIN :", n("SELECT * " + base.format(j="LEFT")), "行")
print("RIGHT JOIN :", n("SELECT * " + base.format(j="RIGHT")), "行")
print("FULL JOIN :", n("SELECT * " + base.format(j="FULL")), "行")
print("CROSS JOIN :", n("SELECT * FROM users u CROSS JOIN orders o"), "行")
print()
print("LEFT JOIN 明细:")
for r in db.execute("SELECT u.name, o.id, o.amount " + base.format(j="LEFT")):
print(" ", r)
实跑输出:
表里: 3 个用户, 4 张单
INNER JOIN : 3 行
LEFT JOIN : 4 行
RIGHT JOIN : 4 行
FULL JOIN : 5 行
CROSS JOIN : 12 行
LEFT JOIN 明细:
('阿呆', 10, 100.0)
('阿呆', 11, 50.0)
('阿瓜', 12, 30.0)
('阿花', None, None)
| 写法 | 保留谁 | 上面为什么是这个数 |
|---|---|---|
INNER JOIN |
只保留配上对的 | 3 张单配上了人(13 号是孤儿,掉了) |
LEFT JOIN |
左表全留,右边没配上的补 NULL |
3 + 阿花那一行 = 4 |
RIGHT JOIN |
右表全留 | 3 + 孤儿单那一行 = 4 |
FULL JOIN |
两边都全留 | 3 + 阿花 + 孤儿单 = 5 |
CROSS JOIN |
不配对,每一对组合都要 | 3 × 4 = 12 |
⭐ LEFT JOIN 补出来的那一行不是空行,是「左边有、右边所有列都是 NULL」的一行。
('阿花', None, None) 里那两个 None 不是数据库里存着的 NULL —— 表里根本没有那一行,
是连接当场造出来的。04 章会讲这个区别为什么要紧。
⚠️ SQLite 3.39.0 起支持 RIGHT / FULL OUTER JOIN;上面 3.50.4 是原文环境记录。MySQL 8.4 仍没有原生 FULL OUTER JOIN,不是“只有老版本没有”。不能把两边 LEFT JOIN 后简单 UNION 当通用等价改写:UNION 会去重,而 SQL 结果通常保留重复;直接 UNION ALL 又可能重复计入匹配行。若需仿真,必须另行验证重复与未匹配行,本节继续使用 SQLite 原生 FULL JOIN,不在这里给未经边界核验的替代代码。RIGHT JOIN 可调换两表用 LEFT JOIN 表达对应保留侧,但列投影与 USING/NATURAL 合列结果也需核对。
版本依据:SQLite 3.39.0 发布说明、MySQL 8.4 JOIN 语法。
🧩 二、ON 和 WHERE,在 LEFT JOIN 上不是一回事
这是全章最容易被忽略、后果又最具体的一条。同样一个 status='paid' 条件,写在 ON 里和写在 WHERE 里,结果完全不同:
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE users (id INT, name TEXT)")
db.execute("CREATE TABLE orders(id INT, user_id INT, amount REAL, status TEXT)")
db.executemany("INSERT INTO users VALUES(?,?)",
[(1, "阿呆"), (2, "阿瓜"), (3, "阿花")])
db.executemany("INSERT INTO orders VALUES(?,?,?,?)",
[(10, 1, 100.0, "paid"), (11, 1, 50.0, "refunded"),
(12, 2, 30.0, "refunded")]) # 阿花一张单都没有
print("① LEFT JOIN,条件写在 ON 里:")
for r in db.execute("SELECT u.name, o.id, o.amount FROM users u "
"LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'paid'"):
print(" ", r)
print("② LEFT JOIN,同样的条件写在 WHERE 里:")
for r in db.execute("SELECT u.name, o.id, o.amount FROM users u "
"LEFT JOIN orders o ON o.user_id = u.id WHERE o.status = 'paid'"):
print(" ", r)
print("③ INNER JOIN + WHERE(和 ② 一模一样):")
for r in db.execute("SELECT u.name, o.id, o.amount FROM users u "
"JOIN orders o ON o.user_id = u.id WHERE o.status = 'paid'"):
print(" ", r)
print("④ 找「一张单都没有」的人 —— WHERE 右表列 IS NULL 是合法用法:")
for r in db.execute("SELECT u.name FROM users u "
"LEFT JOIN orders o ON o.user_id = u.id WHERE o.id IS NULL"):
print(" ", r)
实跑输出:
① LEFT JOIN,条件写在 ON 里:
('阿呆', 10, 100.0)
('阿瓜', None, None)
('阿花', None, None)
② LEFT JOIN,同样的条件写在 WHERE 里:
('阿呆', 10, 100.0)
③ INNER JOIN + WHERE(和 ② 一模一样):
('阿呆', 10, 100.0)
④ 找「一张单都没有」的人 —— WHERE 右表列 IS NULL 是合法用法:
('阿花',)
三行 vs 一行。 原因就是 01 章那张八步表:
| 条件在哪一步生效 | 后果 | |
|---|---|---|
写在 ON 里 |
第①步,配对的时候 | 阿瓜配不上 paid 的单 → 右边补 NULL,⭐ 他这一行还在 |
写在 WHERE 里 |
第②步,配对完之后逐行筛 | 阿瓜那行的 o.status 是 NULL,NULL='paid' 判不出真 → 整行被扔 |
⭐ 判据(按执行顺序记):
ON决定左右行怎样配对;LEFT JOIN再给没有配对的左表行补上右侧NULL;最后WHERE才筛选这些结果行。 只有当WHERE里的右表谓词拒绝NULL时,补出来的行才会被淘汰,此时结果才可能与写成相应INNER JOIN相同。实跑里 ② 和 ③ 一样,是因为o.status = 'paid'在右侧为NULL时不是TRUE。
反过来,接受 NULL 的条件不一定淘汰扩展行。例如 WHERE o.status = 'paid' OR o.id IS NULL 会同时保留「已支付的订单」和「完全没有订单的用户」。所以不能把「右表列出现在 WHERE」直接等同于「必然退化」。
⚠️ 对 INNER JOIN 则完全没区别:两边都要配上对,条件写哪儿结果一样。
💀 所以这个坑是这样长出来的:先写了个 INNER JOIN,条件顺手放进 WHERE,一切正常;
后来需求变成「没有单的人也要出现在报表里」,于是把 INNER 改成 LEFT —— 只改了一个词,WHERE 忘了动。
报表还是少那几个人,而且不报错。
⭐ 一种常见的 NULL 接受写法是第④种:LEFT JOIN 之后检查 WHERE 右表非空键 IS NULL。
它保留的正是右侧没有配对、因而被补上 NULL 的行,可用来做「反连接」(找左边有、右边没有的)。这里应检查右表中「匹配成功时必定非空」的键,例如主键;它不是唯一的例外,也不是在利用所谓的必然退化。05 章还会再用它一次。
同一张用户表,怎样认出发送方和接收方
按天做调用报表,要保留所有事件,同时只在两边身份都查得到的事件中算失败率。回顾同一张表怎样取两个别名和连接前怎样查重复键。上面的例子已经看到,左连接补出的 NULL 行可以被 WHERE 筛掉。
ON 的匹配键。s 是发送方别名,r 是接收方别名。依据:SQLite 的 FROM 与连接规则。先保留事件,再标记是否合格。 用户键唯一时,两次 LEFT JOIN 后每个事件保留一行。遇到查不到的身份,该角色的匹配列填 NULL。把两边身份都匹配的条件放进计数,缺身份的事件仍计入每日总数。依据:SQLite 的 WHERE 筛选规则。
主线|逐行追踪两个身份
示例用户表只有三行:1 是 Lin,2 是 Xu,3 是 Zhou。A、B 是两天。每条调用事件有唯一编号 event_id;status 只有 failed(失败)和 ok(成功)。这份演示日志允许写入用户表里找不到的编号 99。
| 事件 | 日 | 发送方 | 接收方 | 状态 |
|---|---|---|---|---|
| 101 | A | 1 → 1 | 2 → 2 | 失败 |
| 102 | A | 2 → 2 | 3 → 3 | 成功 |
| 103 | A | 3 → 3 | 99 → NULL | 失败 |
| 104 | B | 99 → NULL | 3 → 3 | 失败 |
A 日总共 3 条。只有 101 和 102 两边都配上,合格分母是 2。它们中只有 101 失败,合格失败数是 1。合格失败率等于合格失败数除以合格事件数,所以 A 日为 1 ÷ 2 = 0.5。B 日总共 1 条,合格失败和合格事件都为 0,分母为 0 时失败率记作 NULL(无可计算样本)。
| 日 | 总数 | 合格失败 | 合格分母 | 合格失败率 |
|---|---|---|---|---|
| A | 3 | 1 | 2 | 0.5 |
| B | 1 | 0 | 0 | NULL |
计数前先核对一行代表几条事件。COUNT(*) 统计输入行数。假如去掉用户键的唯一约束,多放一行用户 1,事件 101 的发送方就能配到两行,相同事件会进入日报两次。此时 A 日统计变成总数 4、合格失败 2、分母 3、失败率 2 ÷ 3。依据:SQLite 主键约束与SQLite 的 COUNT 语义。
工程|用 SQLite 复算日报和重复键反例
用 Python 3 的标准库 sqlite3 运行下面的完整程序。它在内存中创建演示数据,先打印连接明细和日报,再试插入重复用户。最后另建一张允许重复键的用户表,用同一个查询重算。
joined 回传两个匹配键。marked 用 CASE 把每行的合格标记写成 1 或 0。daily_counts 按日相加,只有同时合格且失败才给分子加 1。末尾乘 1.0 让除法保留小数,NULLIF(eligible, 0) 把零分母转成 NULL。依据:SQLite 的 SUM、表达式、CASE 与除法和NULLIF。
import sqlite3
from contextlib import closing
def summarize(db, user_table):
# The two callers choose a fixed local table name.
joined = f'''
SELECT e.event_id, e.day, e.status,
s.user_id AS sender_match, r.user_id AS recipient_match
FROM call_events AS e
LEFT JOIN {user_table} AS s ON e.sender_id = s.user_id
LEFT JOIN {user_table} AS r ON e.recipient_id = r.user_id
'''
rows = db.execute(joined + " ORDER BY e.event_id").fetchall()
daily = db.execute(f'''
WITH joined AS ({joined}),
marked AS (
SELECT day, status,
CASE WHEN sender_match IS NOT NULL
AND recipient_match IS NOT NULL
THEN 1 ELSE 0 END AS is_eligible
FROM joined
),
daily_counts AS (
SELECT day, COUNT(*) AS total,
SUM(CASE WHEN is_eligible = 1 AND status = 'failed'
THEN 1 ELSE 0 END) AS eligible_failures,
SUM(is_eligible) AS eligible
FROM marked
GROUP BY day
)
SELECT day, total, eligible_failures, eligible,
1.0 * eligible_failures / NULLIF(eligible, 0) AS failure_rate
FROM daily_counts
ORDER BY day
''').fetchall()
return rows, daily
with closing(sqlite3.connect(":memory:")) as db:
db.executescript('''
CREATE TABLE users (user_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO users VALUES (1, 'Lin'), (2, 'Xu'), (3, 'Zhou');
CREATE TABLE call_events (
event_id INTEGER PRIMARY KEY, day TEXT NOT NULL,
sender_id INTEGER, recipient_id INTEGER,
status TEXT NOT NULL CHECK (status IN ('failed', 'ok'))
);
INSERT INTO call_events VALUES
(101, 'A', 1, 2, 'failed'),
(102, 'A', 2, 3, 'ok'),
(103, 'A', 3, 99, 'failed'),
(104, 'B', 99, 3, 'failed');
''')
rows, daily = summarize(db, "users")
assert rows == [
(101, 'A', 'failed', 1, 2), (102, 'A', 'ok', 2, 3),
(103, 'A', 'failed', 3, None), (104, 'B', 'failed', None, 3),
]
assert daily == [('A', 3, 1, 2, 0.5), ('B', 1, 0, 0, None)]
print("rows:", rows)
print("daily:", daily)
try:
db.execute("INSERT INTO users VALUES (1, 'Lin copy')")
except sqlite3.IntegrityError as error:
print("duplicate:", error)
else:
raise AssertionError("Expected a primary-key violation")
db.executescript('''
CREATE TABLE dirty_users (user_id INTEGER NOT NULL, name TEXT NOT NULL);
INSERT INTO dirty_users SELECT user_id, name FROM users;
INSERT INTO dirty_users VALUES (1, 'Lin copy');
''')
dirty_rows, dirty_daily = summarize(db, "dirty_users")
dirty_ids = [row[0] for row in dirty_rows]
assert dirty_ids == [101, 101, 102, 103, 104]
assert dirty_daily == [('A', 4, 2, 3, 2 / 3), ('B', 1, 0, 0, None)]
print("dirty event_ids:", dirty_ids)
print("dirty daily:", dirty_daily)运行时 daily 每行依次打印日、总数、合格失败、合格分母、合格失败率。Python 将 SQL 的 NULL 显示为 None。
rows: [(101, 'A', 'failed', 1, 2), (102, 'A', 'ok', 2, 3), (103, 'A', 'failed', 3, None), (104, 'B', 'failed', None, 3)]
daily: [('A', 3, 1, 2, 0.5), ('B', 1, 0, 0, None)]
duplicate: UNIQUE constraint failed: users.user_id
dirty event_ids: [101, 101, 102, 103, 104]
dirty daily: [('A', 4, 2, 3, 0.6666666666666666), ('B', 1, 0, 0, None)]检查点|把合格条件移到 WHERE 会怎样
如果在分组前加 WHERE sender_match IS NOT NULL AND recipient_match IS NOT NULL,A 日的总数和失败率各是多少?B 日还会出现吗?
👀 答案
A 日只剩 101、102 两条,所以总数变成 2,失败率仍为 1 ÷ 2 = 0.5。B 日唯一的 104 被筛掉,按事件日期分组时 B 日整行消失。原来的条件计数让 B 日保留总数 1,同时用 NULL 表示失败率无可计算样本。
第 03 章的调用看板会进一步讲按组做条件聚合,并用独立的调用数据演示如何切换报表粒度。
💀 三、扇出:SUM 从 150 变成 250
症状
两张订单(100 元 + 50 元,真实总额 150),一张标签表。1 号单有两个标签,2 号单有一个。 只是顺手连了一下标签表,总额就变成了 250:
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE orders(id INTEGER PRIMARY KEY, amount REAL)")
db.execute("CREATE TABLE tags (order_id INT, tag TEXT)")
db.executemany("INSERT INTO orders VALUES (?,?)", [(1, 100.0), (2, 50.0)])
db.executemany("INSERT INTO tags VALUES (?,?)", [(1, "vip"), (1, "promo"), (2, "new")])
print("真实总额 =", db.execute("SELECT SUM(amount) FROM orders").fetchone()[0])
print("JOIN 之后 SUM =", db.execute(
"SELECT SUM(amount) FROM orders o JOIN tags t ON t.order_id = o.id").fetchone()[0],
" 参与求和的行数 =", db.execute(
"SELECT COUNT(*) FROM orders o JOIN tags t ON t.order_id = o.id").fetchone()[0])
print()
print("连接后的明细(看 100 出现了几次):")
for r in db.execute("SELECT o.id, o.amount, t.tag FROM orders o JOIN tags t ON t.order_id = o.id"):
print(" ", r)
print()
print("修法① EXISTS(只问在不在,不带行进来) =", db.execute(
"SELECT SUM(amount) FROM orders o "
"WHERE EXISTS (SELECT 1 FROM tags t WHERE t.order_id = o.id)").fetchone()[0])
print("修法② 先把右表聚合成一行一单再连 =", db.execute(
"SELECT SUM(o.amount) FROM orders o "
"JOIN (SELECT order_id, COUNT(*) AS n FROM tags GROUP BY order_id) t "
"ON t.order_id = o.id").fetchone()[0])
print("修法③ 对左表主键去重后再求和 =", db.execute(
"SELECT SUM(amount) FROM (SELECT DISTINCT o.id, o.amount FROM orders o "
"JOIN tags t ON t.order_id = o.id)").fetchone()[0])
print()
print("⚠️ 体检:右表每个键平均带来几行 =", db.execute(
"SELECT COUNT(*)*1.0 / COUNT(DISTINCT order_id) FROM tags").fetchone()[0])
实跑输出:
真实总额 = 150.0
JOIN 之后 SUM = 250.0 参与求和的行数 = 3
连接后的明细(看 100 出现了几次):
(1, 100.0, 'vip')
(1, 100.0, 'promo')
(2, 50.0, 'new')
修法① EXISTS(只问在不在,不带行进来) = 150.0
修法② 先把右表聚合成一行一单再连 = 150.0
修法③ 对左表主键去重后再求和 = 150.0
⚠️ 体检:右表每个键平均带来几行 = 1.5
病因
⭐ 回到 01 章那句话:「现在一行代表什么?」
连接之前,orders 的一行代表一张订单。连接之后,一行代表一个 (订单, 标签) 配对。
100.0 这个金额在结果里物理上出现了两次,SUM 老老实实把它加了两次 —— 它没做错任何事,
是你把「按订单求和」的问题喂给了一张「按配对」的表。
100 + 100 + 50 = 250 ← SUM 看到的
100 + 50 = 150 ← 你想要的
💀 这个错误的全部危险都在「不报错」三个字上。
它不会抛异常、不会返回 NULL、不会给出负数。它给你一个看起来完全合理的、只是偏大的数。
⚠️ 尤其可怕的是偏大的比例不固定 —— 它等于「平均每张单有几个标签」,
今天 1.5 倍,下个月运营多打了一批标签就变成 2.3 倍,趋势图上你只会看到「增长」。
三种修法,怎么选
| 修法 | 写法 | 什么时候用 |
|---|---|---|
⭐ ① EXISTS |
WHERE EXISTS (SELECT 1 FROM tags t WHERE t.order_id=o.id) |
只是想按右表做筛选、右表的列一个也不要。⭐ 首选,语义最清楚 |
| ⭐ ② 先聚合再连 | JOIN (SELECT order_id, COUNT(*) n FROM tags GROUP BY order_id) t ON … |
右表的信息要用(要标签数、要最后一次时间)。先把右表压成「一个键一行」,再连 |
③ DISTINCT 去重 |
SELECT SUM(amount) FROM (SELECT DISTINCT o.id, o.amount FROM …) |
⚠️ 能用但别当默认:DISTINCT 必须带上左表主键才对,而且两张单金额都是 100 时靠 id 才没被合掉 |
⚠️ 第③种最常被误用成 SELECT SUM(DISTINCT amount) —— 那是「把重复的金额去掉」,
两张真实订单都是 100 元时会少算一张。去重要按主键去重,不能按值去重。
事前体检:一行 SQL 判断会不会扇出
⭐ 连之前先量一下右表:同一个连接键平均对应几行。
SELECT COUNT(*) * 1.0 / COUNT(DISTINCT order_id) FROM tags;
本例的平均连接倍数是 1.5,说明结果行数被放大。平均数等于 1 并不能证明逐键一对一:例如左表有 id=1、2,右表有两条 id=1、没有 id=2,内连接仍得到两行,却重复了 id=1。应按左表主键 GROUP BY,检查 HAVING COUNT(*) > 1;也检查未匹配行,不能让漏行和重复互相抵消。
⚠️ 1.5 这个平均值还会骗人:真正决定风险的是最大值,因为长尾那几个键会把误差集中放大:
SELECT MAX(n) FROM (SELECT order_id, COUNT(*) AS n FROM tags GROUP BY order_id);
📋 四、连接键的基数,决定结果的行数
在写 JOIN 之前,先回答一句话:「右表里,同一个连接键最多有几行?」
| 关系 | 结果行数 | 需要小心吗 |
|---|---|---|
| 一对一(用户 ↔ 用户画像) | 不变 | 安全 |
| 一对零一(用户 ↔ 可选的实名信息) | INNER 会变少,LEFT 不变 |
⚠️ 会不会掉行,看你用哪种 |
| 一对多(订单 ↔ 标签 / 会话 ↔ 消息) | 放大 | 💀 扇出的产地 |
| 多对多(文章 ↔ 标签,中间表) | 乘法放大 | 💀💀 最贵的一种 |
⚠️ 多路 JOIN 是相乘,不是相加
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE orders(id INTEGER PRIMARY KEY, amount REAL)")
db.execute("CREATE TABLE tags (order_id INT, tag TEXT)")
db.execute("CREATE TABLE items (order_id INT, sku TEXT)")
db.executemany("INSERT INTO orders VALUES(?,?)", [(1, 100.0), (2, 50.0)])
db.executemany("INSERT INTO tags VALUES(?,?)", [(1, "vip"), (1, "promo"), (2, "new")])
db.executemany("INSERT INTO items VALUES(?,?)",
[(1, "A"), (1, "B"), (1, "C"), (2, "D")])
print("orders 2 行, tags 3 行, items 4 行")
print("orders JOIN tags ->", db.execute(
"SELECT COUNT(*) FROM orders o JOIN tags t ON t.order_id=o.id").fetchone()[0], "行")
print("orders JOIN items ->", db.execute(
"SELECT COUNT(*) FROM orders o JOIN items i ON i.order_id=o.id").fetchone()[0], "行")
print("orders JOIN tags JOIN items ->", db.execute(
"SELECT COUNT(*) FROM orders o JOIN tags t ON t.order_id=o.id "
"JOIN items i ON i.order_id=o.id").fetchone()[0], "行 ⚠️ 是相乘不是相加")
print("此时 SUM(amount) =", db.execute(
"SELECT SUM(o.amount) FROM orders o JOIN tags t ON t.order_id=o.id "
"JOIN items i ON i.order_id=o.id").fetchone()[0], " 真实值 150.0")
实跑输出:
orders 2 行, tags 3 行, items 4 行
orders JOIN tags -> 3 行
orders JOIN items -> 4 行
orders JOIN tags JOIN items -> 7 行 ⚠️ 是相乘不是相加
此时 SUM(amount) = 650.0 真实值 150.0
⭐ 1 号单:2 个标签 × 3 个商品 = 6 行;2 号单:1 × 1 = 1 行。合计 7 行,
于是 100 被加了六次、50 被加了一次 → 650,真实值的 4.3 倍。
💀 两张一对多的表连在同一个主表上,误差是乘出来的,不是加出来的。 ⚠️ 而这正是「订单 + 商品明细 + 优惠券」这种最常见的报表结构。 ⭐ 正确做法是各自先聚合成一行一单,再分别连过来(第三节的修法②),或者干脆写成两条查询。
🧯 五、自连接:把「同一张表的两行」摆到一行上
表连自己没有任何特殊之处,只是必须起别名(否则数据库分不清 id 是哪个 id)。
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE runs(id INT, model TEXT, acc REAL, dt TEXT)")
db.executemany("INSERT INTO runs VALUES(?,?,?,?)", [
(1, "m-a", 0.81, "2026-08-01"),
(2, "m-a", 0.83, "2026-08-02"),
(3, "m-a", 0.79, "2026-08-03"),
(4, "m-b", 0.90, "2026-08-01"),
(5, "m-b", 0.88, "2026-08-02"),
])
# 自连接:把「同一个模型的相邻两天」摆在同一行上
print("相邻两天的差:")
for r in db.execute("""
SELECT a.model, a.dt, a.acc, b.acc, ROUND(b.acc - a.acc, 4)
FROM runs a
JOIN runs b ON b.model = a.model AND b.id = a.id + 1
ORDER BY a.model, a.dt"""):
print(" ", r)
# 自连接的另一个用法:找「同一天里比自己好的行」
print()
print("每个模型的最好一次(自连接反证法):")
for r in db.execute("""
SELECT a.model, a.dt, a.acc
FROM runs a
LEFT JOIN runs b ON b.model = a.model AND b.acc > a.acc
WHERE b.id IS NULL"""):
print(" ", r)
实跑输出:
相邻两天的差:
('m-a', '2026-08-01', 0.81, 0.83, 0.02)
('m-a', '2026-08-02', 0.83, 0.79, -0.04)
('m-b', '2026-08-01', 0.9, 0.88, -0.02)
每个模型的最好一次(自连接反证法):
('m-a', '2026-08-02', 0.83)
('m-b', '2026-08-01', 0.9)
⭐ 第二个查询是「反证法」的标准形状:「找不到比我更好的,我就是最好的」——
LEFT JOIN 一个「比我大」的条件,然后用 WHERE b.id IS NULL 保留没有匹配者的扩展行。这里检查的是匹配成功时必定非空的 b.id,属于常见的 NULL 接受写法。
⚠️ 但请注意第一个查询的做法很脆:它靠 b.id = a.id + 1 假设 id 连续。
删掉一行,链就断了。
⭐ 「和上一行比」这件事的正确工具是窗口函数 LAG(),那是 07 章的正题 ——
本章演示自连接是为了让你看清窗口函数在替你做什么,不是推荐这么写。
可选深入:关注、互关与共同关注,三种连接条件
先把每一条边的方向说清楚
follow(src,dst) 表示 src 关注 dst,是有向边。互关要求反向边也存在;两个用户的共同关注则要求他们指向同一个 dst,并不是他们俩互相关注。这里不允许自关注,复合主键阻止重复边,避免 JOIN 把同一目标重复放大。
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("""CREATE TABLE follow(
src INTEGER NOT NULL, dst INTEGER NOT NULL,
PRIMARY KEY(src,dst), CHECK(src<>dst))""")
db.execute("CREATE INDEX follow_reverse ON follow(dst,src)")
db.executemany("INSERT INTO follow VALUES(?,?)", [
(1,2),(2,1),(1,3),(2,3),(1,4),(2,4),(3,4),(4,1)])
try:
db.execute("INSERT INTO follow VALUES(1,2)")
except sqlite3.IntegrityError:
pass
else:
raise AssertionError("duplicate edge accepted")
mutual = db.execute("""SELECT a.dst FROM follow a JOIN follow b
ON b.src=a.dst AND b.dst=a.src
WHERE a.src=? ORDER BY a.dst""", (1,)).fetchall()
common = db.execute("""SELECT a.dst FROM follow a JOIN follow b
ON a.dst=b.dst WHERE a.src=? AND b.src=? ORDER BY a.dst""", (1,2)).fetchall()
assert mutual == [(2,), (4,)]
assert common == [(3,), (4,)]
assert db.execute("SELECT dst FROM follow WHERE src=99").fetchall() == []
print("1的互关:", mutual, "1/2的共同关注:", common)
db.close()
出边查询沿 (src,dst),入边查询沿 (dst,src);是否采用索引仍看实际执行计划与数据分布。若已有脏数据重复边,先清理并建立唯一性约束;临时 DISTINCT 只能去掉输出重复,不能替代写入规则。这里的共同好友明确采用“共同关注目标”定义,其他好友定义须另写条件。小 SQLite 例验证集合语义,没有证明大规模社交检索的吞吐、分片或热点处理。依据:SQLite 连接输入规则。
🪪 六、半连接与反连接:只问「有没有」
先看问题,不急着背名字:如果只想知道「这个用户有没有订单」,我们并不需要把订单列拿出来,更不希望一个用户因为有三张订单而出现三次。
SQL 标准查询里通常不直接写 SEMI JOIN / ANTI JOIN 关键字。它们是关系运算的语义名称:半连接保留「存在匹配」的左表行,反连接保留「不存在匹配」的左表行。最稳妥的表达分别是 EXISTS 与 NOT EXISTS。
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders(id INTEGER PRIMARY KEY, user_id INTEGER);
INSERT INTO users VALUES (1,'阿呆'),(2,'阿瓜'),(3,'阿花');
INSERT INTO orders VALUES (10,1),(11,1),(12,2),(13,NULL);
""")
ordinary = db.execute("""
SELECT u.id, u.name
FROM users AS u JOIN orders AS o ON o.user_id = u.id
ORDER BY u.id, o.id
""").fetchall()
semi = db.execute("""
SELECT u.id, u.name
FROM users AS u
WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.user_id = u.id)
ORDER BY u.id
""").fetchall()
anti = db.execute("""
SELECT u.id, u.name
FROM users AS u
WHERE NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.user_id = u.id)
ORDER BY u.id
""").fetchall()
print("ordinary:", ordinary)
print("semi :", semi)
print("anti :", anti)
assert ordinary == [(1, "阿呆"), (1, "阿呆"), (2, "阿瓜")]
assert semi == [(1, "阿呆"), (2, "阿瓜")]
assert anti == [(3, "阿花")]
db.close()
完整手算:
- 阿呆匹配订单 10、11,所以普通
JOIN产生 2 行;半连接只问「至少有一行吗」,答案是真,于是只保留阿呆 1 次。 - 阿瓜匹配订单 12,普通连接和半连接都各保留 1 行。
- 阿花没有匹配,半连接不保留;反连接的「不存在匹配」为真,所以保留阿花 1 行。
- 订单 13 的
user_id是NULL,NULL = users.id判不出真,因此不会匹配任何用户。
⭐ 行数判据:半连接和反连接的输出都只来自左表,所以每一条左表输入行最多输出一次。普通
JOIN的输出来自匹配对,右表有几条匹配,左行就可能被展开几次。
三种「没有匹配」写法,不要当成无条件等价
| 写法 | 什么时候可靠 | 边界 |
|---|---|---|
NOT EXISTS | 默认首选;直接表达「不存在匹配行」 | 相关条件必须写完整,但不会被子查询中的 NULL 毒化 |
LEFT JOIN … WHERE o.id IS NULL | o.id 是右表匹配后绝不为 NULL 的主键或非空列 | 若检查的是本来就可空的业务列,会把「匹配到但该列为空」误当成没匹配 |
NOT IN (subquery) | 能证明子查询结果不含 NULL | 只要集合里混入一个 NULL,三值逻辑就可能让全部候选都无法判为真;详见 04、05 章 |
⚠️ JOIN + DISTINCT 有时看起来也能得到「有订单的用户」,但它先制造匹配对,再尝试去重。若选出的左表列不能唯一标识原行,两条真实左行还可能被错误合并;而 EXISTS 从一开始就表达「每条左行只做存在性判断」。至于数据库会把哪种写法改写成哪种物理计划,要看目标数据库和执行计划,本章只下语义结论。
🔎 七、四个安静的语义坑
① USING(col) 是 ON a.col = b.col 的简写,但它会把那一列合并成一列。
SELECT * 时 ON 版给你两个 order_id,USING 版只给一个。⭐ 两边列名一样时 USING 更干净。
② NATURAL JOIN 不要用。 它按「所有同名列」自动连接 ——
💀 哪天有人给两张表都加了一个 updated_at,你的连接条件就悄悄多了一条,行数当场不对,而 SQL 一个字没改。
③ 连接键的存储类型与列亲和性,要一起看。下面的原实验中,a(k)、b(k) 都没有声明类型,因此没有用于这次比较的数值或文本亲和性;它不是已声明 INTEGER 列对 TEXT 列的实验。
import sqlite3
db2 = sqlite3.connect(":memory:")
db2.execute("CREATE TABLE a(k)") # 无类型声明,存什么是什么
db2.execute("CREATE TABLE b(k)")
db2.executemany("INSERT INTO a VALUES(?)", [(1,), (2,)])
db2.executemany("INSERT INTO b VALUES(?)", [("1",), (2,)])
print("a JOIN b ON a.k=b.k ->", db2.execute(
"SELECT a.k, b.k FROM a JOIN b ON a.k=b.k").fetchall())
print("CAST 之后 ->", db2.execute(
"SELECT a.k, b.k FROM a JOIN b ON CAST(a.k AS TEXT)=CAST(b.k AS TEXT)").fetchall())
实跑输出:
a JOIN b ON a.k=b.k -> [(2, 2)]
CAST 之后 -> [(1, '1'), (2, 2)]
⚠️ 上面无类型声明列中的字符串 '1' 与整数 1 没有被亲和性转换成相同类型,所以原结果只剩 [(2, 2)];旧代码、CAST 对照与历史输出均保留。不能据此断言 CSV/JSON 文本对自增整数一律连不上。实际还要看建表声明、存入后的类型和比较表达式。声明 INTEGER 与 TEXT 列时,SQLite 可在比较前对另一侧应用数值亲和性;可转换的文本 '1' 就能与整数 1 配对。
只用同一对值核对这个边界(下面实际执行的是 SQLite,不测性能):
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE a0(k); CREATE TABLE b0(k);
CREATE TABLE ai(k INTEGER); CREATE TABLE bt(k TEXT);
INSERT INTO a0 VALUES(1); INSERT INTO b0 VALUES('1');
INSERT INTO ai VALUES(1); INSERT INTO bt VALUES('1');
""")
plain = db.execute("SELECT a0.k,b0.k FROM a0 JOIN b0 ON a0.k=b0.k").fetchall()
typed = db.execute("SELECT ai.k,bt.k FROM ai JOIN bt ON ai.k=bt.k").fetchall()
assert plain == []
assert typed == [(1, "1")]
assert db.execute("SELECT typeof(ai.k),typeof(bt.k) FROM ai CROSS JOIN bt").fetchone() == ("integer", "text")
print("no affinity:", plain)
print("INTEGER / TEXT:", typed)
db.close()
断言与输出:无类型列 [],INTEGER / TEXT 得到 [(1, '1')];后者存储类型仍是 integer / text,相等来自比较时的转换。两行对照不保证任意脏字符串都会按业务含义相等。应在入库前明确 ID 的类型、格式与转换规则,避免把有前导零等业务标识误当数字。依据:SQLite 比较前的类型转换。
MySQL 8.4 的对应限制也要具体说:字符串列与数字比较时,不能用该字符串列索引作快速值定位;这不等于所有索引或连接路径都失效。其他转换方向、连接策略与访问路径须看目标版本实际计划。此处仅核对官方类型转换说明,本轮没有运行 MySQL。
④ 连接键是 NULL 时永远连不上,哪怕两边都是 NULL。
import sqlite3
db3 = sqlite3.connect(":memory:")
db3.execute("CREATE TABLE x(k, v)")
db3.execute("CREATE TABLE y(k, w)")
db3.executemany("INSERT INTO x VALUES(?,?)", [(1, "x1"), (None, "x-null")])
db3.executemany("INSERT INTO y VALUES(?,?)", [(1, "y1"), (None, "y-null")])
print("INNER JOIN ->", db3.execute("SELECT x.v, y.w FROM x JOIN y ON x.k=y.k").fetchall())
print("LEFT JOIN ->", db3.execute("SELECT x.v, y.w FROM x LEFT JOIN y ON x.k=y.k").fetchall())
print("IS 而不是 = ->", db3.execute("SELECT x.v, y.w FROM x JOIN y ON x.k IS y.k").fetchall())
实跑输出:
INNER JOIN -> [('x1', 'y1')]
LEFT JOIN -> [('x1', 'y1'), ('x-null', None)]
IS 而不是 = -> [('x1', 'y1'), ('x-null', 'y-null')]
⭐ NULL = NULL 判不出真(04 章的正题),所以两个 NULL 键配不上对。
⚠️ 这在多数时候是你想要的(「未知」和「未知」当然不该算同一个人),
但如果你的连接键真的允许 NULL 并且希望它们相等,SQLite 用 IS、Postgres 用 IS NOT DISTINCT FROM。
🔗 这一章连到哪里
| 相关的地方 | 为什么 |
|---|---|
| 01-一条查询是怎么跑的.html | ⭐ 第二节 ON / WHERE 的区别,直接就是那一章八步表的推论:ON 在第①步,WHERE 在第②步 |
| 03-聚合与分组.html | 扇出之所以致命,是因为下一步几乎总是聚合。那一章讲聚合本身怎么算,两章合起来才是完整的账 |
| 04-NULL与三值逻辑.html | ⭐ 本章两处依赖它:LEFT JOIN 补出来的 NULL 会让 WHERE 整行落空;连接键是 NULL 时永远配不上对 |
| 05-子查询的三种形状.html | 扇出的首选修法 EXISTS 在那一章展开:IN / EXISTS / JOIN 到底该怎么选 |
| 07-窗口函数.html | 第五节那个「和上一行比」的自连接很脆(假设 id 连续);LAG() 才是它的正确工具 |
| 09-计划里的连接与索引失效.html | 本章只讲连接的语义;连接的顺序和代价、以及 CAST 为什么会毁掉索引,在那一章 |
| AI 全栈 · 07 向量检索落地 | 站内唯一一条真实业务里的多表 JOIN:过滤条件 archived 在 documents 上、向量在 chunks 上,⭐ 关系库能直接连过去,正是那一章选它的理由 |
✅ 检查点
- 三个用户、四张单(含一张孤儿单、一个没下过单的人)那组实测里,五种连接各返回几行?每个数字怎么来的?
- 同一个
status='paid'条件,写在LEFT JOIN的ON里和WHERE里,实跑分别返回几行?为什么? - 请按顺序解释
ON配对、LEFT JOIN补NULL、WHERE后筛选。什么样的右表谓词才会淘汰未匹配行? LEFT JOIN … WHERE 右表非空键 IS NULL为什么能保留右侧没有匹配的扩展行?为什么不能把它说成唯一例外?- 扇出那组实测里,真实总额是多少,
JOIN之后SUM是多少,参与求和的是几行?用一行算式说明差在哪。 - 为什么说扇出「偏大的比例不固定」?它等于什么?
- 三种修法各在什么时候用?为什么
SUM(DISTINCT amount)是错的? - 连之前怎么一行 SQL 判断会不会扇出?实测那个数是多少?为什么还要看最大值?
- 两张一对多的表连在同一个主表上,实跑的行数和
SUM是多少?和真实值差几倍? - 为什么
NATURAL JOIN不该用?无类型声明列与INTEGER / TEXT列比较同一对1 / '1'时,SQLite 结果为何不同?MySQL 的字符串列对数字比较限制,具体针对哪个索引用途? - 阿呆两张单、阿瓜一张单、阿花没单的例子里,普通连接、半连接、反连接分别返回哪些用户?为什么半连接不会把阿呆重复两次?
- 为什么反连接默认优先
NOT EXISTS?LEFT JOIN … IS NULL检查哪类列才可靠,NOT IN又会被什么值毒化?
👀 答案
INNER3(3 张单配上了人,孤儿单掉了)、LEFT4(3 + 阿花补NULL那行)、RIGHT4(3 + 孤儿单)、FULL5(3 + 阿花 + 孤儿单)、CROSS12(3 × 4,不配对)。- 写在
ON里 3 行(阿呆有 paid 单,阿瓜和阿花右边补NULL,行还在); 写在WHERE里 1 行,和INNER JOIN + WHERE输出一模一样。 因为ON在第①步配对时生效,WHERE在第②步逐行筛 —— 补出来的NULL让NULL='paid'判不出真,整行被扔。 ON决定配对,LEFT JOIN给未匹配的左表行补右侧NULL,WHERE再筛选结果。 只有拒绝这些NULL的右表谓词才会淘汰未匹配行,此时结果才可能与相应INNER JOIN相同;接受NULL的条件(例如带OR ... IS NULL)仍可能保留扩展行。 💀 常见来路:本来是INNER JOIN(条件写哪儿都一样),后来需求变成「没单的人也要出现」, 把INNER改成LEFT—— 只改了一个词,WHERE忘了动,报表还是少人且不报错。- 因为它是一个常见的
NULL接受条件:先LEFT JOIN让没配上的补NULL, 再用WHERE 右表非空键 IS NULL把这些「右边没有」的挑出来。要检查匹配成功时必定非空的列;IS NULL不是唯一能保留扩展行的条件。实跑返回('阿花',)。 - 真实 150.0,
JOIN之后 250.0,参与求和 3 行。100 + 100 + 50 = 250(100 出现两次)而不是100 + 50 = 150—— 因为一行不再代表一张订单,而是一个(订单, 标签)配对。 - 因为放大倍数 = 平均每张单有几个标签。今天 1.5 倍,运营多打一批标签就变 2.3 倍, ⚠️ 趋势图上只会看到「增长」。
EXISTS—— 只想按右表筛选、右表的列一个不要(首选);先聚合再连 —— 右表的信息要用(标签数、最后时间);DISTINCT去重 —— 能用但别当默认,必须带上左表主键。 ⚠️SUM(DISTINCT amount)是「把重复的金额去掉」,两张真实订单都是 100 元时会少算一张 —— 要按主键去重,不能按值去重。SELECT COUNT(*)*1.0/COUNT(DISTINCT order_id) FROM tags,实测 1.5。大于 1 就会扇出。 还要看MAX(n),因为长尾那几个键会把误差集中放大,平均值看不出来。orders(2) JOIN tags(3) JOIN items(4)→ 7 行(1 号单 2 × 3 = 6,2 号单 1 × 1 = 1),SUM(amount)= 650.0,是真实值 150 的 4.3 倍。💀 误差是乘出来的,不是加出来的。NATURAL JOIN按所有同名列自动连接 —— 哪天有人给两张表都加了updated_at, 连接条件就悄悄多一条、行数当场不对,而 SQL 一个字没改。 本例无类型声明列不做数值/文本亲和性转换,'1'与1不匹配,原两行实验只剩[(2, 2)]。声明INTEGER / TEXT后,可转换文本可按比较亲和性配对,新增单值对照为[(1, '1')]。MySQL 8.4 所述限制针对字符串列与数字比较时该字符串索引的快速值定位;其他方向或访问路径需查实际计划,本轮未运行 MySQL。- 普通连接返回阿呆两次、阿瓜一次,共 3 行;半连接返回阿呆、阿瓜,共 2 行;反连接只返回阿花,共 1 行。半连接的判据是「右边是否至少存在一条匹配」,输出仍是左表行,每条左行最多一次;它不会枚举全部匹配对。
NOT EXISTS直接表达「不存在匹配」,不会因右表集合里出现NULL而整体失效。LEFT JOIN … WHERE o.id IS NULL只有在o.id是匹配后必定非空的主键或非空列时才可靠;检查可空业务列会误判。NOT IN的子查询只要混入一个NULL,三值逻辑就可能让所有候选都无法判为真。
本节回顾
⚡ 走神救援
⭐
JOIN不是「拼表」,是按条件配对,配出来的行数可能比任何一张原表都多。⭐ 第一条要背的是执行顺序:
ON决定配对,LEFT JOIN给未匹配行补右侧NULL,WHERE最后筛选。 若WHERE中的右表谓词拒绝NULL,未匹配行会消失,结果才可能与相应INNER JOIN相同。💀 常见事故是需求从「只看有订单的人」改成「没单的人也要出现」时,只把INNER改成LEFT,却保留了会拒绝NULL的WHERE条件,报表少人且不报错。⭐ 若条件接受NULL,例如检查一个匹配成功时必定非空的右表键IS NULL,或使用能让扩展行成立的OR,这些行仍可保留;反连接只是这种语义的一种常见应用,不是唯一例外。💀💀 本章的正题是扇出:顺手连一下标签表,求和就变大了——因为一行不再代表一张订单,而是一个「订单加标签」的配对。⚠️ 危险全在「不报错」三个字上:不抛异常、不返回空值,只给一个看起来合理、只是偏大的数;而且放大倍数不固定,趋势图上只会显示「增长」。
三种修法:⭐
EXISTS只筛不取列(首选)、⭐ 先把右表聚合成一行一键再连、DISTINCT按主键去重(别当默认)。⚠️ 按值去重是错的——两张真实订单金额相同会少算一张。事前体检一行 SQL 就够:总行数除以去重主键数,大于 1 就会扇出;⚠️ 还要看最大值,长尾那几个键会集中放大。💀 多路
JOIN是相乘不是相加——而「订单加明细加优惠券」正是最常见的报表结构。⭐ 只问右边「有没有」时,不要先展开再去重:有匹配用半连接语义
EXISTS,没匹配用反连接语义NOT EXISTS。两者都只输出左表行,每条左行最多一次。LEFT JOIN … IS NULL要检查右表必定非空的键;NOT IN则必须先证明子查询没有NULL。另外四个安静的坑:
USING会合并同名列;💀NATURAL JOIN不要用(有人加个时间戳字段,连接条件就悄悄多一条);⚠️ 连接键要同时核存储类型、列亲和性与转换规则:无类型列的失败不能推广到 INTEGER/TEXT;MySQL 字符串列对数字比较的快速索引定位限制,也不是所有路径失效;连接键是 NULL 永远配不上对。
下一节 👉 03-聚合与分组.md