🏠 总目录📚 本教程 02 · JOIN 的几种语义 ← →
📑 本页目录(点开跳转)

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 章还会再用它一次。


沿用原页三个用户:阿呆有paid和refunded订单,阿瓜只有refunded订单,阿花无订单。paid条件放ON时保留阿呆的paid单并为另两人补NULL,得到三行;放WHERE时只剩阿呆的paid单一行。
LEFT JOIN:在哪一步筛 — 同样的右表条件 o.status = 'paid':放在 ON 得 3 行,放在 WHERE 得 1 行。该结论针对这个拒绝 NULL 的条件;不要推广成所有 WHERE 条件都会消除外连接。 单独打开图示

同一张用户表,怎样认出发送方和接收方

按天做调用报表,要保留所有事件,同时只在两边身份都查得到的事件中算失败率。回顾同一张表怎样取两个别名和连接前怎样查重复键。上面的例子已经看到,左连接补出的 NULL 行可以被 WHERE 筛掉。

两个角色各自指向同一张用户表 调用事件表的 sender_id 经左连接匹配 users 的 user_id,别名为 s。recipient_id 经另一条左连接匹配同一列,别名为 r。 call_events sender_id recipient_id users s.user_id r.user_id
顺着两条箭头看 ON 的匹配键。s 是发送方别名,r 是接收方别名。依据:SQLite 的 FROM 与连接规则。

先保留事件,再标记是否合格。 用户键唯一时,两次 LEFT JOIN 后每个事件保留一行。遇到查不到的身份,该角色的匹配列填 NULL。把两边身份都匹配的条件放进计数,缺身份的事件仍计入每日总数。依据:SQLite 的 WHERE 筛选规则。

主线|逐行追踪两个身份

示例用户表只有三行:1 是 Lin,2 是 Xu,3 是 Zhou。A、B 是两天。每条调用事件有唯一编号 event_id;status 只有 failed(失败)和 ok(成功)。这份演示日志允许写入用户表里找不到的编号 99。

两次左连接后的四行。箭头左边是日志里的编号,右边是匹配到的身份键。
事件日发送方接收方状态
101A1 → 12 → 2失败
102A2 → 23 → 3成功
103A3 → 399 → NULL失败
104B99 → NULL3 → 3失败

A 日总共 3 条。只有 101 和 102 两边都配上,合格分母是 2。它们中只有 101 失败,合格失败数是 1。合格失败率等于合格失败数除以合格事件数,所以 A 日为 1 ÷ 2 = 0.5。B 日总共 1 条,合格失败和合格事件都为 0,分母为 0 时失败率记作 NULL(无可计算样本)。

日报的每一行对应一个日期
日总数合格失败合格分母合格失败率
A3120.5
B100NULL

计数前先核对一行代表几条事件。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()

完整手算:

  1. 阿呆匹配订单 10、11,所以普通 JOIN 产生 2 行;半连接只问「至少有一行吗」,答案是真,于是只保留阿呆 1 次。
  2. 阿瓜匹配订单 12,普通连接和半连接都各保留 1 行。
  3. 阿花没有匹配,半连接不保留;反连接的「不存在匹配」为真,所以保留阿花 1 行。
  4. 订单 13 的 user_id 是 NULL,NULL = users.id 判不出真,因此不会匹配任何用户。

⭐ 行数判据:半连接和反连接的输出都只来自左表,所以每一条左表输入行最多输出一次。普通 JOIN 的输出来自匹配对,右表有几条匹配,左行就可能被展开几次。

三种「没有匹配」写法,不要当成无条件等价

写法什么时候可靠边界
NOT EXISTS默认首选;直接表达「不存在匹配行」相关条件必须写完整,但不会被子查询中的 NULL 毒化
LEFT JOIN … WHERE o.id IS NULLo.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 上,⭐ 关系库能直接连过去,正是那一章选它的理由

✅ 检查点

  1. 三个用户、四张单(含一张孤儿单、一个没下过单的人)那组实测里,五种连接各返回几行?每个数字怎么来的?
  2. 同一个 status='paid' 条件,写在 LEFT JOIN 的 ON 里和 WHERE 里,实跑分别返回几行?为什么?
  3. 请按顺序解释 ON 配对、LEFT JOIN 补 NULL、WHERE 后筛选。什么样的右表谓词才会淘汰未匹配行?
  4. LEFT JOIN … WHERE 右表非空键 IS NULL 为什么能保留右侧没有匹配的扩展行?为什么不能把它说成唯一例外?
  5. 扇出那组实测里,真实总额是多少,JOIN 之后 SUM 是多少,参与求和的是几行?用一行算式说明差在哪。
  6. 为什么说扇出「偏大的比例不固定」?它等于什么?
  7. 三种修法各在什么时候用?为什么 SUM(DISTINCT amount) 是错的?
  8. 连之前怎么一行 SQL 判断会不会扇出?实测那个数是多少?为什么还要看最大值?
  9. 两张一对多的表连在同一个主表上,实跑的行数和 SUM 是多少?和真实值差几倍?
  10. 为什么 NATURAL JOIN 不该用?无类型声明列与 INTEGER / TEXT 列比较同一对 1 / '1' 时,SQLite 结果为何不同?MySQL 的字符串列对数字比较限制,具体针对哪个索引用途?
  11. 阿呆两张单、阿瓜一张单、阿花没单的例子里,普通连接、半连接、反连接分别返回哪些用户?为什么半连接不会把阿呆重复两次?
  12. 为什么反连接默认优先 NOT EXISTS?LEFT JOIN … IS NULL 检查哪类列才可靠,NOT IN 又会被什么值毒化?
👀 答案
  1. INNER 3(3 张单配上了人,孤儿单掉了)、LEFT 4(3 + 阿花补 NULL 那行)、 RIGHT 4(3 + 孤儿单)、FULL 5(3 + 阿花 + 孤儿单)、CROSS 12(3 × 4,不配对)。
  2. 写在 ON 里 3 行(阿呆有 paid 单,阿瓜和阿花右边补 NULL,行还在); 写在 WHERE 里 1 行,和 INNER JOIN + WHERE 输出一模一样。 因为 ON 在第①步配对时生效,WHERE 在第②步逐行筛 —— 补出来的 NULL 让 NULL='paid' 判不出真,整行被扔。
  3. ON 决定配对,LEFT JOIN 给未匹配的左表行补右侧 NULL,WHERE 再筛选结果。 只有拒绝这些 NULL 的右表谓词才会淘汰未匹配行,此时结果才可能与相应 INNER JOIN 相同;接受 NULL 的条件(例如带 OR ... IS NULL)仍可能保留扩展行。 💀 常见来路:本来是 INNER JOIN(条件写哪儿都一样),后来需求变成「没单的人也要出现」, 把 INNER 改成 LEFT —— 只改了一个词,WHERE 忘了动,报表还是少人且不报错。
  4. 因为它是一个常见的 NULL 接受条件:先 LEFT JOIN 让没配上的补 NULL, 再用 WHERE 右表非空键 IS NULL 把这些「右边没有」的挑出来。要检查匹配成功时必定非空的列;IS NULL 不是唯一能保留扩展行的条件。实跑返回 ('阿花',)。
  5. 真实 150.0,JOIN 之后 250.0,参与求和 3 行。 100 + 100 + 50 = 250(100 出现两次)而不是 100 + 50 = 150 —— 因为一行不再代表一张订单,而是一个 (订单, 标签) 配对。
  6. 因为放大倍数 = 平均每张单有几个标签。今天 1.5 倍,运营多打一批标签就变 2.3 倍, ⚠️ 趋势图上只会看到「增长」。
  7. EXISTS —— 只想按右表筛选、右表的列一个不要(首选);先聚合再连 —— 右表的信息要用(标签数、最后时间); DISTINCT 去重 —— 能用但别当默认,必须带上左表主键。 ⚠️ SUM(DISTINCT amount) 是「把重复的金额去掉」,两张真实订单都是 100 元时会少算一张 —— 要按主键去重,不能按值去重。
  8. SELECT COUNT(*)*1.0/COUNT(DISTINCT order_id) FROM tags,实测 1.5。大于 1 就会扇出。 还要看 MAX(n),因为长尾那几个键会把误差集中放大,平均值看不出来。
  9. orders(2) JOIN tags(3) JOIN items(4) → 7 行(1 号单 2 × 3 = 6,2 号单 1 × 1 = 1), SUM(amount) = 650.0,是真实值 150 的 4.3 倍。💀 误差是乘出来的,不是加出来的。
  10. NATURAL JOIN 按所有同名列自动连接 —— 哪天有人给两张表都加了 updated_at, 连接条件就悄悄多一条、行数当场不对,而 SQL 一个字没改。 本例无类型声明列不做数值/文本亲和性转换,'1' 与 1 不匹配,原两行实验只剩 [(2, 2)]。声明 INTEGER / TEXT 后,可转换文本可按比较亲和性配对,新增单值对照为 [(1, '1')]。MySQL 8.4 所述限制针对字符串列与数字比较时该字符串索引的快速值定位;其他方向或访问路径需查实际计划,本轮未运行 MySQL。
  11. 普通连接返回阿呆两次、阿瓜一次,共 3 行;半连接返回阿呆、阿瓜,共 2 行;反连接只返回阿花,共 1 行。半连接的判据是「右边是否至少存在一条匹配」,输出仍是左表行,每条左行最多一次;它不会枚举全部匹配对。
  12. 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

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