如何改写LEFT JOIN？此INNER JOIN+UNION ALL方式是否通用？

我最近尝试把LEFT JOIN拆分成「INNER JOIN结果 + 左表中未匹配右表的记录」的形式，比如下面这个例子：

-- 原表创建与数据插入
create table lt (id1 int, val1 string);
insert into lt VALUES (1, "one"), (2, "two"), (3, "three");
create table rt (id2 int, val2 string);
insert into rt VALUES (2, "two"), (3, "three"), (4, "four");

-- 原LEFT JOIN查询
select * from lt left join rt on id1=id2;

原查询结果：

+-----+-------+------+-------+
| id1 | val1  | id2  | val2  |
+-----+-------+------+-------+
| 1   | one   | NULL | NULL  |
| 2   | two   | 2    | two   |
| 3   | three | 3    | three |
+-----+-------+------+-------+

我把它改写成了这样：

select lt.*, NULL as id2, NULL as val2 
from lt where id1 not in (select id2 from rt) 
union all 
select * from lt join rt on id1=id2;

改写后的结果和原查询一致，但我不确定这种改写方式是不是通用的？是不是所有LEFT JOIN都能这么拆？有没有更简洁的改写方法？

回答 #

这个拆分思路在大多数场景下是通用的，但有几个容易踩的小坑，另外也有更简洁、更安全的替代写法：

一、通用性的前提与注意事项 #

这种拆分逻辑完全贴合LEFT JOIN的定义：左表所有记录，加上右表匹配的记录；不匹配的右表字段用NULL填充。不过要注意两个特殊场景：

• 右表关联字段存在NULL值：如果rt.id2有NULL，id1 NOT IN (SELECT id2 FROM rt)会直接返回空结果（SQL里NOT IN遇到NULL会导致整个条件不成立），这时候拆分后的查询就会漏掉左表未匹配的记录。这种情况下，把NOT IN换成NOT EXISTS就没问题了：

  select lt.*, NULL as id2, NULL as val2 
  from lt 
  where NOT EXISTS (SELECT 1 FROM rt WHERE rt.id2 = lt.id1)
  union all 
  select * from lt join rt on id1=id2;
  

• 多字段关联的LEFT JOIN：如果你的JOIN条件是多个字段（比如lt.a = rt.a AND lt.b = rt.b），NOT IN就没法直接用了，这时候同样要用NOT EXISTS来判断“左表记录在右表中没有匹配项”：

  select lt.*, NULL as a, NULL as b, NULL as val2 
  from lt 
  where NOT EXISTS (SELECT 1 FROM rt WHERE rt.a = lt.a AND rt.b = lt.b)
  union all 
  select lt.*, rt.a, rt.b, rt.val2 from lt join rt on lt.a = rt.a AND lt.b = rt.b;
  

二、更简洁安全的改写方法 #

其实有个更直观的写法，用LEFT JOIN + WHERE 右表字段 IS NULL来获取左表未匹配的记录，再和INNER JOIN结果合并，避开了NOT IN的陷阱：

-- 用LEFT JOIN获取不匹配部分，替代NOT IN
select lt.*, NULL as id2, NULL as val2 
from lt left join rt on lt.id1 = rt.id2 
where rt.id2 IS NULL
union all 
select * from lt join rt on lt.id1 = rt.id2;

这种写法逻辑和你的思路完全一致，但通用性更强，不用考虑NULL的问题。

另外，如果你的数据库支持EXCEPT（比如PostgreSQL）或MINUS（比如Oracle），还可以用集合差来获取左表独有的记录，不过可读性不如上面的写法：

-- PostgreSQL/Oracle示例
select lt.*, NULL as id2, NULL as val2 
from (select * from lt except select lt.* from lt join rt on lt.id1 = rt.id2) as lt
union all 
select * from lt join rt on lt.id1 = rt.id2;

三、总结 #

• 你的拆分思路是完全符合LEFT JOIN本质的，在关联字段无NULL、单字段关联的场景下100%通用；遇到NULL或多字段关联时，换成NOT EXISTS或者LEFT JOIN ... WHERE IS NULL就能保证通用性。
• 更简洁的替代写法首推LEFT JOIN ... WHERE IS NULL + UNION ALL + INNER JOIN，它既直观又避开了NOT IN的坑。

内容的提问来源于stack exchange，提问作者facha