qinghuan
← 回到知识探索

后端现场 / 动手实验

只有三张订单，为什么 JOIN 后查出六行？

一张订单配上多条明细，就会在结果里出现多次。先看每一行代表什么，再决定报表该数谁。

约 6 分钟含页面互动实验资料核对：2026-09-27
展开本文目录
  1. 先动手看看
  2. 配对时，一张订单可以出现好几次
  3. 没有明细的订单，要不要出现在结果里？
  4. 三个 COUNT，回答三个不同问题
  5. 在内存里亲手对照五行与六行
  6. 筛选条件的位置，也会改变保留谁
  7. 记住这三点
  8. 别把例子用过头
  9. 自己试一小步
  10. 查原始资料
  11. 找一份资料继续练
开始前，会这些就够了
  • 认识表、主键和简单 SELECT；示例使用 Python 3.10+ 标准库
遇到不熟悉的词，先看这 1 个解释
JOIN（表联接）
按条件把两张表的行配在一起。一行可以与另一边多行配对，因此结果行数可能增加。
打开完整术语小抄 ↗

订单表里只有三张订单，查完联接却看到六行。先别急着怀疑数据库复制了订单：查询结果的一行，可能已经从“一张订单”变成“一张订单配一条明细”。

本例的订单编号是 1、2、3，各有 0、2、3 条明细。订单和明细编号都唯一、非空；只按订单编号匹配。我们不修改原订单数，而是换角度观察同一组数据。

三张订单，联接后变成几行？

订单编号固定为 1、2、3。每条明细有自己的非空编号，只属于一张订单。先改明细数，再比较两种联接。

原始明细表：这张订单没有明细

明细编号：21、22

明细编号：31、32、33

全部联接结果：6 行，其中真实明细 5 条，出现过的不同订单 3 张。

已选订单 1：保留 1 行，明细编号为 NULL；这是占位，不是新明细。

窄屏可在结果表内左右滑动；键盘用户可聚焦表格区域后按方向键。

联接结果明细；“已选”仅标记查看对象，不筛掉其他订单
订单编号明细编号这一行表示什么
订单 1 · 已选NULL（没有匹配）占位行，不是明细
订单 221一对匹配的订单与明细
订单 222一对匹配的订单与明细
订单 331一对匹配的订单与明细
订单 332一对匹配的订单与明细
订单 333一对匹配的订单与明细
COUNT(*)
6 行（含占位）
COUNT(i.id)
5 条非 NULL 明细
COUNT(DISTINCT o.id)
3 张结果中出现的订单

匹配条件是 o.id = i.order_id，没有后续筛选。联接只生成查询结果，不新增订单。这里按同一规则展示行，并没有在浏览器运行 SQL；排序固定为订单编号、明细编号。

配对时，一张订单可以出现好几次

JOIN（联接）把两张表中符合条件的行放到一起。这里的条件 o.id = i.order_id，意思是“订单编号等于明细上记的订单编号”；o 和 i 只是两张表的简短别名。

订单 2 有两条明细，所以形成两对；订单 3 有三条，形成三对。订单编号重复出现，是每对匹配都要写出所属订单，不代表又创建了订单。这里就是常说的“一对多”：一张订单对应多条明细。

没有明细的订单，要不要出现在结果里？

INNER JOIN 只留下匹配成功的配对，因此本例得到五行，订单 1 不在其中。它没有从原表消失，只是没有进入这次结果。

LEFT JOIN 除了保留这些配对，还为左边没有匹配的订单补一行。订单 1 因此重新出现，右边的明细列填 NULL，结果共有六行。这行表示“保留这张订单，但没找到明细”，不是编号为 NULL 的真实明细。

三个 COUNT，回答三个不同问题

COUNT(*) 数结果的全部行，包含 LEFT JOIN 补出的占位行。COUNT(i.id) 只数明细编号不是 NULL 的行；在本例的两表关系下，每条真实明细恰好出现一次，所以它正好是明细数。

COUNT(DISTINCT o.id) 先把结果中的订单编号去重，再数个数。它数的是“当前结果中出现过的订单”，不是无条件数原订单表。INNER JOIN 没带进来的订单 1，无法靠 DISTINCT 再找回来。

如果要算每张订单平均有几条明细，也先说清分母：只算有明细的订单，还是把没有明细的订单也算上。这是两种业务问题，不能只看哪个 COUNT 写起来顺手。

在内存里亲手对照五行与六行

下面用 Python 自带的 SQLite 建两张临时内存表。输出先列出每对编号，再按“结果行、明细、不同订单”的顺序打印统计。Python 读到 SQL 的 NULL 时显示为 None。

ORDER BY 只是让结果按编号排列，便于逐行核对；没有它时不要依赖返回顺序。代码中的联接方式来自固定的 INNER／LEFT 两个值，并没有接收外部输入。

小实验 · python
import sqlite3
from contextlib import closing


def main():
    with closing(sqlite3.connect(":memory:")) as db:
        db.executescript("""
            CREATE TABLE orders (id INTEGER PRIMARY KEY);
            CREATE TABLE items (
                id INTEGER PRIMARY KEY,
                order_id INTEGER NOT NULL REFERENCES orders(id)
            );
        """)
        db.executemany("INSERT INTO orders VALUES (?)", [(1,), (2,), (3,)])
        db.executemany("INSERT INTO items VALUES (?, ?)",
                       [(21, 2), (22, 2), (31, 3), (32, 3), (33, 3)])
        for mode in ("INNER", "LEFT"):
            # mode comes only from this fixed pair, not user input.
            joined = f"FROM orders o {mode} JOIN items i ON o.id = i.order_id"
            rows = db.execute(
                "SELECT o.id, i.id " + joined + " ORDER BY o.id, i.id"
            ).fetchall()
            counts = db.execute(
                "SELECT COUNT(*), COUNT(i.id), COUNT(DISTINCT o.id) " + joined
            ).fetchone()
            print(mode + " rows:", rows)
            print(mode + " counts (rows, items, orders):", counts)


if __name__ == "__main__":
    main()

怎样运行：Python 3.10+ 标准库；保存为 join_counts.py，运行 python join_counts.py。只创建内存表，不联网。

运行后对照结果
INNER rows: [(2, 21), (2, 22), (3, 31), (3, 32), (3, 33)]
INNER counts (rows, items, orders): (5, 5, 2)
LEFT rows: [(1, None), (2, 21), (2, 22), (3, 31), (3, 32), (3, 33)]
LEFT counts (rows, items, orders): (6, 5, 3)

筛选条件的位置，也会改变保留谁

LEFT JOIN 后再写 WHERE i.id >= 100，本例所有结果都被排除，连占位行也保不住，因为 NULL 与 100 比较不会得到真。把同一个限制写进 ON，则是在决定哪些明细能配上；没有配上的订单仍会各保留一行。

所以“写了 LEFT JOIN”并不保证最终结果一定包含所有订单，还要看后续筛选。DISTINCT 也不是统计错误的万能补丁：先确定你要数订单、明细还是配对，再选择字段与范围。

记住这三点

  • 一对多联接会让同一订单出现在多个结果行中，原订单表没有因此增加记录。
  • LEFT JOIN 的 NULL 占位行会被 COUNT(*) 数到，但不是真实明细。
  • COUNT(DISTINCT 订单号) 只数当前结果中的不同订单；先说明统计对象与分母。

别把例子用过头

  • 本例只联接订单与一张明细表。再联接其他一对多表，明细也可能重复出现，不能继续把 COUNT(i.id) 直接当唯一明细数。
  • 用来数真实明细的 i.id 在原表里是非空主键；换成允许 NULL 的备注等字段，会漏数本来存在的明细。
  • 这里只解释结果语义，不展示执行计划、索引性能或生产数据库中的全部数据约束。

自己试一小步

如果把订单 2 的明细清空，再给订单 1 新增一条明细，两种联接和三种统计会怎样变化？

  1. 先在纸上为每张订单列出配对；没有匹配的订单单独标记。
  2. 只改内存表的插入数据，分别运行 INNER 与 LEFT，对照你的预测。
  3. 为一个“平均每单明细数”报表写一句说明，明确是否包含没有明细的订单。

做到这些，就算完成

  • 能指出哪些输出行是真明细，哪些只是占位。
  • 能够解释原订单数、结果行数和结果中不同订单数的区别。
  • 没有通过删除重复订单号的行，误删本来应该保留的明细。

查原始资料

想确认一个细节，先看这份资料对应的部分，不必从头读完所有文档。

找一份资料继续练

先选一项，按下面的范围继续学；不必从头读完整门课程。