LEFT JOIN 到底什么时候会被优化器偷偷改写成 INNER JOIN

写 LEFT JOIN 的时候，大部分人心里的预期都很朴素：左表的数据无论如何都会保留，右表匹配不上就是 NULL。这个预期在大多数场景下是成立的，但只要 WHERE 子句里对右表字段加了一个过滤条件，这个预期就可能悄悄崩掉——SQL 文本里写的明明是 LEFT JOIN，执行计划里跑出来的却是一个不折不扣的内连接，结果集也跟着变。

这篇不复现业务场景，直接用最干净的两张表把这个机制的边界条件挨个测一遍：什么条件会触发这种改写、什么条件不会、条件放在不同位置结果差多少。环境还是 KES V009R001C010，业务账号连接：

ksql -h 127.0.0.1 -p 54321 -U app_user -d app_db

搭一个最小的测试台

两张表，t_oje_t1 是左表，t_oje_t2 是右表：

create table app_schema.t_oje_t1 (
  id1 integer primary key,
  name1 varchar(30) not null
);


create table app_schema.t_oje_t2 (
  id2 integer primary key,
  id1 integer not null,
  name2 varchar(30) not null
);


insert into app_schema.t_oje_t1(id1, name1) values
  (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');


insert into app_schema.t_oje_t2(id2, id1, name2) values
  (101, 1, 'aa'),
  (102, 2, 'bb'),
  (103, 2, 'cc'),
  (104, 3, 'cc');

数据故意设计成这样：t1 有 4 行，id1=4 在 t2 里完全没有匹配（模拟"右表缺失"），id1=2 在 t2 里对应两条记录（bb 和 cc，模拟一对多）。后面会看到，这个"一对多"的设计埋了一个很关键的坑，先卖个关子。

第一步：右表条件放 WHERE，外连接直接没了

执行计划第一行是 Hash Join，注意——不是 Hash Left Join。KES 这边只要真按外连接执行，计划里都会老老实实带上 Left 这个字样，这里没有，说明这条 LEFT JOIN 在优化阶段就已经被改写成了普通内连接。往下看，t_oje_t2 被 Seq Scan 扫描，带了个 Filter: (name2)::text = 'cc'::text)，Rows Removed by Filter: 2——t2 总共 4 行，过滤后只剩 2 行（id1=2 的 cc、id1=3 的 cc），这 2 行再去跟 t1 做 Hash Join，最终 actual rows=2。

道理很直白：t2.name2 = 'cc' 这个条件，对 id1=1（没有 cc 匹配）和 id1=4（t2 里压根没数据，字段全是 NULL）来说，NULL = 'cc' 的结果既不是真也不是假，是"未知"，在 WHERE 里"未知"就等于被扔掉。既然外连接产生的这些 NULL 行反正都要被过滤掉，那"外连接 + 这个过滤"和"内连接 + 这个过滤"结果完全一样，优化器一看这笔账划算，就直接换成开销更小的内连接去跑了。这就是外连接消除。

这次执行计划显示的是 Hash Right Join，Filter: (t2.name2 IS NULL)，Rows Removed by Filter: 4，最终 actual rows=1——也就是 id1=4 那一行。

这里有个细节容易让人愣一下：计划里写的是 Right Join，不是 Left Join。这不是消除，是优化器把驱动表和探测表的位置换了一下（先扫 t2 建 hash 表，再拿 t1 去探测），但语义上 t1 LEFT JOIN t2 和这里的 t2 做 build 端、t1 做 probe 端的 Right Join 是完全等价的外连接，只是物理执行顺序反过来了，外连接的"保底"语义一点没丢。跟第一步的 Hash Join（没有 Left/Right 字样，纯内连接）完全是两码事，不要混在一起看。

为什么这次不能消除：IS NULL 这个条件本身就是专门用来抓外连接产生的 NULL 行的。如果把外连接改成内连接，t2 里没匹配的行根本进不了结果集，t2.name2 is null 就永远不可能为真，这已经不是"用更快的方式得到同样结果"，是彻底改变了查询语义，所以优化器不会碰这种条件。

判断标准到这儿就很清楚了：能不能消除，看这个 WHERE 条件对外连接产生的 NULL 值判定成什么——一定是假或未知（比如普通等值比较），消除是安全的；有可能判定成真（比如 IS NULL），消除就会改变结果，优化器不会做。

这次执行计划是 Nested Loop Left Join，Left 字样老老实实地在，是真正的外连接。Join Filter: (t1.id1 = t2.id1)，t_oje_t1 先按 Filter: (name1)::text = 'b'::text 筛出 1 行（id1=2），再拿这一行去跟 t2 做外连接，最终 actual rows=2——因为 id1=2 在 t2 里有两条匹配（bb、cc），一行左表数据关联出两行结果，这跟外连接消除完全不搭边。

这一步的过滤本质上是"先决定左表要哪些行，再拿这些行去外连接"，跟右表的 Nullable 特性没有任何关系。很多人容易把"WHERE 里出现的任何条件"都当成外连接消除的诱因，这一步就是用来打破这个误解的对照组。

这次执行计划是 Hash Left Join，Left 字样稳稳地在，actual rows=4。跟第四步不一样的地方在于：这次的过滤条件 t2.name2='cc' 作用在右表上，它会先把 t2 过滤成只剩满足 name2='cc' 的行（id1=2 的 cc、id1=3 的 cc，id1=2 的 bb 被挡在外面），过滤完的 t2 每个 id1 最多只剩一条，再拿去跟 t1 做外连接，t1 的 4 行每行正好对应 1 行结果，id1=1、id1=4 的 t2 字段是 NULL，id1=2、id1=3 有值。

对比第一步就很清楚了：同样是想找"关联到 name2='cc' 的记录"，条件放 WHERE 会把 t1 里没匹配上的行连带杀掉（外连接被消除），条件放 ON 才能既过滤右表又保住左表全量。这是这篇最该记住的一条规则：对右表的过滤，只要目的是保留外连接语义，就该放 ON，不要放 WHERE。而对左表的过滤（第四步），放 ON 和放 WHERE 效果是不一样的——放 WHERE 会先筛左表再连接，放 ON 只决定"筛出来的左表行允不允许连"，右表该出几行还是出几行，两者不能混着记。

这次是 Hash Left Join，actual rows=4，和第五步条件下推到 ON 的结果完全一致。这说明 (+) 写法底层走的是同一套判断逻辑，(+) 只是外连接的另一种语法糖，不代表写了它外连接就一定被保留——过滤条件要不要跟着写 (+)，效果跟"条件放 WHERE 还是放 ON"是对应的。

这一点对从 Oracle 迁移过来的读者尤其要注意：老代码里如果只在连接条件上写了 (+)，后面又单独加了一个不带 (+) 的过滤条件，一样会被判定为可以安全消除成内连接。

收个尾：怎么在自己的 SQL 里审计这个问题