今天整理了一份 JOIN 的分步课件,存进了博客。
单文件 HTML,没有外部依赖,双击就能在浏览器里跑。
它怎么用
页面顶上一句提示:点「下一步」逐句执行,点「上一步」回看。每一页都是一次真实的执行结果快照。
- 蓝底的行是这一步新增的 SQL
- 上方可以展开原始数据——两张表的全部内容,是执行任何 SQL 之前的样子,没有经过任何过滤。每一页的结果都是从这两张表算出来的,想核对某一行为什么出现或消失,回到这里查它的原始字段值
- 左右方向键也能切换
数据是竖表模式的两张表:person_business_relation(企业与人的关系)和 person_kv(动态字段,一行一个字段)。左边六行里混着三种干扰项——R3 是店铺(business_type = 2)、R4 属于别的企业、R5 关联已删除。筛完之后剩三个人:P1、P2、P6,后面所有题都围着他们转。
这三个人是特意配的:P1 填了两个字段、P2 只填了手机号、P6 一条字段都没填。「填了」和「没填」的每一种组合,都能在这三个人身上找到。
八道题
题 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 次数越多,这是竖表模式的代价。前面七题都在教「怎么把竖表查对」,最后一句提醒的是「查对是有成本的」。用着方便的设计,通常把复杂度藏在了查询这一侧。