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，外连接直接消失

explain analyzeselect * from app_schema.t_oje_t1 t1left join app_schema.t_oje_t2 t2 on t1.id1 = t2.id1where t2.name2 = 'cc';

执行计划第一行是 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 行反正都要被过滤掉，那“外连接 + 这个过滤”和“内连接 + 这个过滤”结果完全一样，优化器一看这笔账划算，就直接换成开销更小的内连接去跑了。这就是外连接消除（Outer Join Elimination）。

第二步：换成 IS NULL，结果完全反过来

explain analyzeselect * from app_schema.t_oje_t1 t1left join app_schema.t_oje_t2 t2 on t1.id1 = t2.id1where t2.name2 is 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），消除就会改变结果，优化器不会做。

第三步：条件换到左表，跟外连接消除没关系

explain analyzeselect * from app_schema.t_oje_t1 t1left join app_schema.t_oje_t2 t2 on t1.id1 = t2.id1where t1.name1 = 'b';

这次执行计划是 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 里出现的任何条件”都当成外连接消除的诱因，这一步就是用来打破这个误解的对照组。

第四步：把左表条件挪进 ON，结果比想象中多了一行

到这一步本来想验证的是：左表条件放进 ON，会不会跟“右表条件放 WHERE”一样有什么隐藏效应。

select * from app_schema.t_oje_t1 t1left join app_schema.t_oje_t2 t2  on t1.id1 = t2.id1 and t1.name1 = 'b';

原本设想的结果是 4 行——t1 全量保留，只是 name1 <> 'b' 的行 t2 字段全是 NULL。跑出来一看，是 5 行，多了一行。仔细看输出：id1=2 这一行出现了两次，一次对应 id2=102/bb，一次对应 id2=103/cc；id1=1、3、4 各出现一次，t2 相关字段全是 NULL。

想明白这事之后觉得挺合理的：t1.name1='b' 这个条件放进 ON，只是告诉优化器“只有 name1='b' 的左表行才允许去匹配右表”，它管的是“允不允许连”，不管“连上以后右边能出几行”。id1=2 满足 name1='b'，于是它就拿着 id1=2 去 t2 里找所有 id1=2 的记录——而 t2 里 id1=2 本来就有两条，两条全都会被连出来，一条也不会因为“外连接只保底一行”就被合并掉。左连接的“保底”只保证左表这一行至少出现一次，从没保证只出现一次。

这跟前面认为的“4 行”错在哪儿：想当然地把“左表 4 行”和“结果 4 行”划了等号，却忘了右表本来就有一对多的数据。这个坑其实挺常见，很多人写业务 SQL 的时候，只要 JOIN 的右表存在一对多关系，加不加条件、条件放哪，行数都可能跟“左表有几行”对不上，得先确认清楚右表对应关系，再看结果对不对得上。

第五步：右表条件挪进 ON，才是真正保住外连接语义的写法

explain analyzeselect * from app_schema.t_oje_t1 t1left join app_schema.t_oje_t2 t2  on t1.id1 = t2.id1 and t2.name2 = 'cc';

这次执行计划是 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 只决定“筛出来的左表行允不允许连”，右表该出几行还是出几行，两者不能混着记。

第六步：Oracle(+)写法，同样的规则换个皮

KES 兼容 Oracle 风格的 (+) 外连接写法，实测看它是不是遵循同一套规则：

explain analyzeselect * from app_schema.t_oje_t1 t1, app_schema.t_oje_t2 t2where t1.id1 = t2.id1(+)  and t2.name2 = 'cc';

执行计划是 Hash Join，没有 Left/Right 字样，actual rows=2，跟第一步的 WHERE 写法结果一模一样——过滤条件不带 (+)，外连接照样被消除。再看过滤条件也带上 (+) 的写法：

explain analyzeselect * from app_schema.t_oje_t1 t1, app_schema.t_oje_t2 t2where t1.id1 = t2.id1(+)  and t2.name2(+) = 'cc';

这次是 Hash Left Join，actual rows=4，和第五步条件下推到 ON 的结果完全一致。这说明 (+) 写法底层走的是同一套判断逻辑，(+) 只是外连接的另一种语法糖，不代表写了它外连接就一定被保留——过滤条件要不要跟着写 (+)，效果跟“条件放 WHERE 还是放 ON”是对应的。这一点对从 Oracle 迁移过来的读者尤其要注意：老代码里如果只在连接条件上写了 (+)，后面又单独加了一个不带 (+) 的过滤条件，一样会被判定为可以安全消除成内连接。

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

跑一遍下来，判断标准可以归成几条：

1. 看 SQL 文本里有没有 LEFT/RIGHT JOIN 或 (+)，这是审计的起点。
2. 跑一遍 EXPLAIN ANALYZE，盯住连接节点是不是明确带 Left/Right 字样。不带的话基本可以确定被消除了。
3. 对照 WHERE 子句，看是不是对右表（Nullable 一侧）字段做了等值、范围、IN 之类的过滤，且没用 IS NULL/IS NOT NULL——这类条件是触发消除的典型信号。
4. 右表过滤条件想保住外连接语义，下推到 ON；(+) 写法下也是同样道理，过滤条件要不要带 (+)，跟对应关系走。
5. 左表条件放 ON 和放 WHERE 不是一回事：放 WHERE 是先筛左表再连接，放 ON 只决定这行左表能不能参与连接，右表该出几行还是出几行——尤其右表存在一对多关系时，千万别拿左表行数直接套右表行数。

这几条规则说到底都是一件事：外连接消不消除，从来不取决于你写没写 LEFT，而取决于这个查询的整体逻辑，跟内连接放在一起算，结果是不是完全一样。只要有一丁点不一样（哪怕只是多保留一个 NULL 行的可能性），优化器就不会去消除；只要完全一样，它就一定会去消除，图的就是内连接更便宜这点执行开销。写 SQL 的时候脑子里想的是“业务要不要保底”，数据库执行的时候看的是“逻辑上能不能划等号”，这中间的落差，就是这一类坑的根源。