日记:2026/10/25

·7 min read·

一份 JOIN 分步课件——ON 与 WHERE 的分界线,八道题把竖表查询的坑走一遍

今天整理了一份 JOIN 的分步课件,存进了博客。

👉 打开分步课件:JOIN 分步课件

单文件 HTML,没有外部依赖,双击就能在浏览器里跑。

它怎么用

页面顶上一句提示:点「下一步」逐句执行,点「上一步」回看。每一页都是一次真实的执行结果快照。

  • 蓝底的行是这一步新增的 SQL
  • 上方可以展开原始数据——两张表的全部内容,是执行任何 SQL 之前的样子,没有经过任何过滤。每一页的结果都是从这两张表算出来的,想核对某一行为什么出现或消失,回到这里查它的原始字段值
  • 左右方向键也能切换

数据是竖表模式的两张表:person_business_relation(企业与人的关系)和 person_kv(动态字段,一行一个字段)。左边六行里混着三种干扰项——R3 是店铺(business_type = 2)、R4 属于别的企业、R5 关联已删除。筛完之后剩三个人:P1、P2、P6,后面所有题都围着他们转。

这三个人是特意配的:P1 填了两个字段、P2 只填了手机号、P6 一条字段都没填。「填了」和「没填」的每一种组合,都能在这三个人身上找到。

八道题

#题目标签
1查出已经填了「名当地语言」的人基础
2查出没填「名当地语言」的人核心
3陷阱:field_key 写进 WHERE 会怎样核心
4统计每人字段条数,没填显示 0GROUP BY
5查出完全没有填写任何动态字段的人反连接
6导出全部人员 + 字段值,没填也出现业务场景
7字段值是空字符串,算不算没填边界
8竖表转横表:两个字段并排进阶

题 1 结尾就埋了钩子:INNER JOIN 下,field_key 写在 ON 还是 WHERE,结果完全一样——差别只在换成 LEFT JOIN 时才暴露。题 2 和题 3 就是在拆这个。

三个真正需要记住的结论

一、右表的条件必须写在 ON。

题 3 把同一套 LEFT JOIN 里的 field_key 条件从 ON 挪到 WHERE,逐行淘汰:P6 的 NULL 行先被删掉(NULL = 值 为 UNKNOWN),接着 P2 的 mobile 行也被删。结果 LEFT JOIN 又变回了 INNER JOIN。

规律:WHERE 里只要出现「右表列 = 非 NULL 值」,LEFT JOIN 就退化成 INNER JOIN。 想让左表的人保留,右表条件必须写在 ON。

二、LEFT JOIN 之后数数,要写具体列名。

COUNT(*) 数的是行数,不是字段数——P6 那一行是 LEFT JOIN 补出来的 NULL 行,也被算成了 1,结果「没填任何字段的人」显示成了 1 条。改成 COUNT(k.id) 才是对的:COUNT(列名) 跳过 NULL,COUNT(*) 不跳过,用主键 k.id 最稳。

三、两种「空」不是一回事。

题 7 加了一行 K7:P2 有一条「名当地语言」,但值是空字符串。它和「没有这一行」是两回事——界面上看起来都是空,SQL 里一个 IS NULL 成立、一个不成立。

只用 IS NULL 会漏掉 P2,因为 P2 确实有这一行,只是值为空。要覆盖两种空:

k.id IS NULL OR k.field_value = ''

题 5 还顺带说了一条反连接的写法:NOT EXISTS 和 LEFT JOIN + IS NULL 结果相同,但不要用 NOT IN——如果子查询结果里出现 NULL,NOT IN 会让整个查询返回空。

题 6 是日常最会用到的

企业详情的「相关人士」列表就是这种查询:每个人一行,没填的留空。

它和题 2 只差一个 IS NULL——题 2 只要没填的人,题 6 要所有人。中间那步最容易误解:把字段条件放进 ON 之后,P2 显示 NULL,而不是它的手机号。因为 ON 已经把 mobile 那行排除在配对之外了,LEFT JOIN 只能补一个 NULL。

题 8 讲竖表转横表:每个字段加一次 LEFT JOIN,各自带自己的 field_key 条件,用 k1、k2 区分。为什么全是 LEFT JOIN——任一字段缺失时,只该让那一列变 NULL,不能把整个人弄丢。字段越多 JOIN 越多,课件里直接点明:这是竖表模式的代价。

一点体会

LEFT JOIN 的这个坑,看过很多次文字说明,但每次都是在脑子里推演。

这份课件最好的一点,是把同一份基础数据摆在那里不动,然后一步步改 SQL,让结果集一行一行地少。P6 的 NULL 行被删掉的那一刻,是能直接看见的——不用想象「会退化成 INNER JOIN」是什么意思,屏幕上就摆着那个后果。

还有一处设计我觉得值得学:三个人物的数据是刻意配好的。P1 填两个、P2 填一个、P6 填零,这个组合把「填了 / 没填 / 部分填」三种状态一次性覆盖了。测试数据不是随便编的,是按要探索的边界反推出来的——这样每一道题的答案都足够小,能一眼看完,又足够典型,能把问题暴露出来。

最后,第八题结尾那句话是整份课件的落点:字段越多,JOIN 次数越多,这是竖表模式的代价。前面七题都在教「怎么把竖表查对」,最后一句提醒的是「查对是有成本的」。用着方便的设计,通常把复杂度藏在了查询这一侧。

Twitter