LEFT JOIN 变 INNER JOIN

三个用户,为何只剩一个?

章节

  • 0:00 开场
  • 0:32 一句话回答
  • 1:04 痛点
  • 1:33 拆开执行过程
  • 2:26 逻辑处理顺序
  • 3:20 三值逻辑
  • 3:46 拒绝 NULL 的条件
  • 4:47 正确写法
  • 5:51 ON 和 WHERE 对比
  • 6:52 反向陷阱
  • 7:22 四种需求 · 四种写法
  • 8:49 粒度陷阱
  • 9:32 优化器改写
  • 10:58 一棵决策树
  • 11:38 面试回答模板
  • 12:16 高频追问
  • 13:59 总结

文稿

开场

明明写的是 LEFT JOIN,三个用户却只查出一个;SQL 没报错,只是把一个条件写进了 WHERE。很多人背过一句口诀:LEFT JOIN 后,WHERE 里不能写右表条件,否则会变成 INNER JOIN。这句话能帮你避坑,但并不严谨。面试官真正想听的是:ON 和 WHERE 分别在哪一步生效?补出来的 NULL 行为什么会被删?什么条件会拒绝 NULL,什么条件不会?既要保留左表,又要限制右表,SQL 该怎么写?

一句话回答

一句话回答:LEFT JOIN 先保留左表全部记录,没匹配上的,右表字段补成 NULL。随后 WHERE 里如果写了「右表字段等于某个值」这类拒绝 NULL 的条件,补齐行的条件结果不是 TRUE,就被删掉,结果在语义上就和 INNER JOIN 相同。要保留左表全部记录,就把右表过滤条件放进 ON,或者先过滤右表,再做 LEFT JOIN。注意关键词是拒绝 NULL:IS NULL、某些带 OR 或 COALESCE 的条件,并不会简单地变成 INNER JOIN。

痛点

先看痛点。用户表 dim_user 有三个人:小王、小李、小张。订单表 dwd_order 有三笔:小王一笔已支付、一笔已取消,小李一笔已取消。需求是:查全部用户,展示他们的已支付订单;没有已支付订单的用户也要保留。很多人这样写:LEFT JOIN 订单表,ON 用户 ID 相等,WHERE 订单状态等于 PAID。结果只返回小王的 O101,小李和小张都不见了。

拆开执行过程

把过程拆开看。第一步,ON 只按用户 ID 匹配:小王匹配到两笔订单,得到两行。小李匹配到他那笔已取消的订单;小张一笔订单都没有,右表字段全部补成 NULL。LEFT JOIN 之后一共四行,三个用户都还在。第二步,WHERE 逐行检查:只有小王的 PAID 订单通过。两笔已取消订单,条件是 FALSE,被删除;小张的 NULL 行,条件是 UNKNOWN,同样被删除。这里纠正原稿一个说法:小李并没有补 NULL,他匹配到了已取消订单,是被 FALSE 删掉的。真正补 NULL 的只有小张。原因不同,结果一样:左表用户被 WHERE 删掉了。打个比方:LEFT JOIN 像点名册,没交作业的人也先留着,作业一栏填空;WHERE 又说只留作业已完成的人,刚保留的空白记录,自然又被删掉。

逻辑处理顺序

要理解这道题,得看 SQL 的逻辑处理顺序,而不是书写顺序。第一步,读取左表;第二步,按 ON 条件寻找右表的匹配记录;第三步,没匹配上的左表记录,生成一条右侧全是 NULL 的补齐行;第四步,WHERE 对 Join 结果整体过滤;第五步,才是 SELECT、聚合、排序。最关键的一点:补 NULL 发生在 ON 匹配之后,WHERE 过滤又发生在补 NULL 之后。所以 ON 里写状态等于 PAID,意思是只有已支付订单,才有资格和用户匹配;WHERE 里写同样的条件,意思是 Join 完以后,只留下状态为已支付的结果行。一句话:ON 决定右表哪些行能参与匹配,WHERE 决定最终哪些行能活下来。注意这是逻辑顺序。优化器可以调整物理执行,但结果必须和逻辑语义一致。

三值逻辑

核心原因:SQL 并不只有 TRUE 和 FALSE,它用三值逻辑,还有 UNKNOWN。PAID 等于 PAID,结果是 TRUE;CANCELED 等于 PAID,结果是 FALSE。NULL 等于 PAID,结果是 UNKNOWN,不是 FALSE。WHERE 只保留 TRUE,FALSE 和 UNKNOWN 都会被过滤。未匹配用户的订单状态是 NULL,比较结果是 UNKNOWN,这一行就消失了。

拒绝 NULL 的条件

什么叫拒绝 NULL 的条件?右表字段为 NULL 时,条件不可能得到 TRUE,就是拒绝 NULL。比如:状态等于 PAID,pay_amount 大于 0,支付时间大于等于 8 月 1 日。还有 order_id IN 一个列表,甚至状态不等于 CANCELED。把 NULL 代进去,结果全是 UNKNOWN,放在 WHERE 里,都会删掉补齐行。优化器甚至可能直接把外连接改写成内连接,因为在这个条件下,两者结果等价。但下面三种,不能套用同一个结论。第一种,order_id IS NULL:它专门找右表没匹配上的行,补齐行反而全部保留。第二种,COALESCE 先把 NULL 换成 PAID,再判断等于 PAID,补齐行也能通过。第三种,用户等级等于 VIP,OR 订单状态等于 PAID。右表为空时,左表条件仍可能让整个 OR 为 TRUE。所以更严谨的说法是:WHERE 里加右表的拒绝 NULL 条件,LEFT JOIN 才会失去保留未匹配行的效果。

正确写法

正确写法:把状态条件挪进 ON,写成用户 ID 相等,AND 订单状态等于 PAID。现在小王能匹配 O101。小李没有满足 ON 条件的已支付订单,右表补 NULL;小张完全没有订单,同样补 NULL。后面没有 WHERE 再来删,三个用户全部保留。对比一下:条件写在 ON,右表先限制成 PAID,再和全部用户匹配,没匹配上的补 NULL 保留;条件写在 WHERE,先关联全部状态的订单,再过滤结果,NULL 补齐行被删除。也可以先过滤右表,再做 LEFT JOIN:用 WITH 先选出已支付订单,再和用户表关联。对只引用右表字段的简单过滤,这和放进 ON,表达的是同一个业务语义。右表过滤和预聚合比较复杂时,这种写法更清楚。性能上别只看外观。以 Spark 3.5 为例,LEFT JOIN 的 ON 里只引用右表的条件,会被下推到右表一侧。两种写法常常得到同样的执行计划,谁更快,要看 EXPLAIN。

ON 和 WHERE 对比

那 ON 和 WHERE 到底怎么选?逐项对比。作用对象:ON 控制左右表怎么匹配,WHERE 过滤 Join 后的最终结果。对左表未匹配行:ON 下通常继续保留;WHERE 里的右表拒绝 NULL 条件,会把它删除。典型需求:ON 用来展示全部用户及符合条件的订单;WHERE 用来只要符合条件的用户订单。右表范围:ON 里不满足条件的右表行不参与匹配;WHERE 下,右表行可能先匹配,再被过滤。结果语义:ON 仍然以左表为主;WHERE 可能和 INNER JOIN 等价。可读性:ON 适合表达关联资格,WHERE 适合表达最终结果限制。优化器:ON 里的右表条件可能下推;WHERE 拒绝 NULL 时,可能改写成 INNER JOIN。常见风险:ON 里误放左表条件,左表行仍会保留;WHERE 里误删本该保留的左表记录。判断时问自己:什么样的右表记录可以来匹配?最终输出必须满足什么条件?

反向陷阱

还有一个容易忽略的反向陷阱:把左表条件放进 ON,并不会过滤左表记录。比如在 ON 里加上用户等级等于 VIP,本意是只看 VIP 用户。可 LEFT JOIN 照样保留非 VIP 用户,只是他们匹配不到右表,订单列全是 NULL。如果最终只看 VIP 用户,这个条件应该放在 WHERE。可见,ON 不等于所有过滤都提前写,WHERE 也不等于绝对不能写右表字段。关键是先说清业务语义。

四种需求 · 四种写法

四种业务需求,四种写法。需求一:全部用户,以及他们的已支付订单。要保留没有已支付订单的用户,右表条件放 ON。需求二:只看有已支付订单的用户。不需要保留未匹配用户,直接写 INNER JOIN 更诚实。别明明想要 INNER JOIN,却绕成 LEFT JOIN 加 WHERE。结果可能相同,但读代码的人,会误判你的业务意图。需求三:找出没有已支付订单的用户,这是 Anti Join 的思路。写法一:LEFT JOIN,ON 里带上 PAID,WHERE 判断 order_id IS NULL,留下小李和小张。写法二更推荐:NOT EXISTS,直接表达对当前用户,不存在满足条件的订单。用 IS NULL 判断时,最好选右表业务上不为空的键,比如 order_id。如果拿允许为空的普通属性判断,就会把匹配上了、但属性为空的行,误判成没匹配到。需求四:统计每个用户的已支付订单数。不能随手写 COUNT 星号。没有已支付订单的用户,也有一条补齐行,COUNT 星号会数成 1。COUNT order_id 不统计 NULL,才会得到正确的 0。四种需求收成一张图:要保留全部左表,用 LEFT JOIN,右表条件放 ON;只要匹配成功的,用 INNER JOIN;只找没匹配的,用 NOT EXISTS,或 LEFT JOIN 后判断右表键 IS NULL。

粒度陷阱

还要小心:条件放对了,指标也可能算错。把状态条件挪进 ON,只解决了左表用户被误删,没解决一对多 Join 带来的重复行。一个用户有三笔已支付订单,LEFT JOIN 后就是三行,这是正确的一对多结果。但直接 SUM 用户粒度的 user_score,同一个分数会被每笔订单复制一遍,算成三倍。这个膨胀问题前面专题讲过:先在各自粒度统计,再按目标粒度 Join,或者根本不关联订单表。右表只用来判断有没有订单,就别用普通 JOIN 展开多行,用 EXISTS 或半连接。LEFT JOIN 保住了左表,不代表保住了指标口径。写完 Join,再问一句:现在一行代表什么?

优化器改写

补充一个概念:写的是 LEFT JOIN,物理执行计划里不一定还是外连接。假设 WHERE 里有 pay_amount 大于 0。右表没匹配时,pay_amount 是 NULL,条件不可能为 TRUE。那些为 LEFT JOIN 生成的补齐行,最终一定会被删除,保留它们没有意义。于是优化器做等价改写,物理计划直接用 INNER JOIN。以 Spark 3.5 为例,这条规则叫 EliminateOuterJoin。它取出 WHERE 里只引用右表的条件,把右表字段全部代成 NULL 算一遍;结果是 NULL 或 FALSE,左外连接就改成内连接。条件还必须是确定性的。MySQL 8.0 的官方文档把这类条件叫 null-rejected:对任何补 NULL 的行,都得到 FALSE 或 UNKNOWN。WHERE 条件是 null-rejected,外连接就被替换成内连接。Hive 4.0 开启 CBO 时,默认就是开启的,基于 Calcite 的 Filter Join 规则也会做同样的简化。为什么要改?外连接限制了表的访问顺序,改成内连接,优化器能考虑更多 Join 顺序。而且外连接上方的右表条件推不下去;变成内连接后,就能下推到右表扫描。这不是数据库偷偷改坏了 SQL,而是基于等价语义的优化。排查时同时看四件事:业务意图对不对,WHERE 条件是否拒绝 NULL,EXPLAIN 里 Join 类型有没有被改写,实际行数是否符合预期。

一棵决策树

用一棵决策树收住。先问:是否必须保留左表未匹配的记录?不需要,就用 INNER JOIN。需要,就用 LEFT JOIN。接着问:条件描述的是右表的匹配资格吗?是,就写进 ON,或者先过滤右表。如果描述的是最终输出,就检查:右表为 NULL 时,条件能不能为 TRUE?不能,会删掉未匹配行,要重新确认需求;能,可以放 WHERE,但要验证 NULL 语义。只想判断是否存在:存在用 EXISTS 或半连接;不存在用 NOT EXISTS,或右表非空键 IS NULL。需要右表明细,就用普通 LEFT JOIN,再检查一对多粒度。

面试回答模板

面试怎么答?先把题目说严谨:WHERE 里用右表的拒绝 NULL 条件时,结果才和 INNER JOIN 等价。第一,LEFT JOIN 按 ON 匹配,未匹配的左表记录仍保留,右表字段补成 NULL。第二,WHERE 在补 NULL 之后执行;右表状态等于 PAID,遇到 NULL 得到 UNKNOWN。WHERE 只保留 TRUE,补齐行就被删除。第三,要保留全部左表、只关联符合条件的右表,就把右表条件写进 ON,或者先过滤右表再 Join。如果本来只要匹配成功的数据,我会直接写 INNER JOIN。最后还要检查 Join 后的粒度,避免一对多导致指标重复。

高频追问

追问一:所有写在 WHERE 的右表条件,都会让 LEFT JOIN 失效吗?不会,关键看是否拒绝 NULL。等于 PAID、大于 0,会排除补齐行;IS NULL 反过来专门保留未匹配行。COALESCE 可以让补齐行通过;带 OR 的表达式,也要结合另一侧条件判断。方法只有一个:把右表字段代入 NULL,看整个表达式能不能得到 TRUE。追问二:右表条件放 ON 和放子查询,性能一定一样吗?只引用右表字段的简单过滤,语义通常相同,优化器也常把过滤下推到扫描阶段。但复杂子查询可能带聚合、窗口、LIMIT、非确定性函数,改写后未必等价;不同引擎的优化能力也不同。先选业务语义最清楚的写法,再用 EXPLAIN 看扫描量、谓词下推和 Join 策略。追问三:找没有订单的用户,为什么推荐 NOT EXISTS?它直接表达:对当前用户,不存在满足条件的订单,不依赖某个右表字段是否为 NULL。用 LEFT JOIN 时,要把状态条件放进 ON,再在 WHERE 判断右表一个业务上不为空的键。两种写法都可能被优化成 Anti Join:Spark 把 NOT EXISTS 改写成 Left Anti Join;Hive 4.0 也能把 LEFT JOIN 加 IS NULL,转成 Anti Join。追问四:为什么 LEFT JOIN 后,统计数量常常多出 1?没匹配上的左表行仍会产生一条结果,COUNT 星号统计结果行,把补齐行算成 1。COUNT order_id 只数非 NULL 的订单键,没有订单时得到 0。如果右表键本身可能重复,还要按口径预聚合,或者用 COUNT DISTINCT,别把保留左表和订单数量混为一谈。

总结

最后总结:ON 先决定怎么匹配;LEFT JOIN 再给未匹配的左表记录补 NULL;WHERE 最后过滤 Join 结果,只保留 TRUE;右表的拒绝 NULL 条件会删掉补齐行,结果和 INNER JOIN 等价。要保留左表,右表匹配条件放 ON;只要匹配行,直接 INNER JOIN;判断不存在,优先 NOT EXISTS。Join 类型写对以后,还要继续检查一对多和指标粒度。记住最实用的一句话:不是右表条件绝对不能写 WHERE,而是先问:右表为空时,这条左表记录还该不该保留?

看完去做题