添加NULL检查后非NULL行消失，PostgreSQL JOIN逻辑疑问

背景表结构 #

创建了两张测试表：

create table community(id,actor_id)as values
 (151492, 'https://lemm.ee/c/gameboy')
,(627389, 'https://lemmy.world/c/gameboy')
,(55519,  'https://lemmy.world/c/gameboy');
create table duplicate_community_rows_by_name_actor_id(name,actor_id)as values
 ('actor1','https://lemmy.world/c/gameboy');

实际环境中（PostgreSQL 16），duplicate_community_rows_by_name_actor_id表是通过以下方式创建并导入数据的：

create temporary table duplicate_community_rows_by_name_actor_id(
  name text
, actor_id text);

copy pg_temp.duplicate_community_rows_by_name_actor_id 
  from '/var/lib/postgresql/data/duplicates.csv' 
  delimiter ',' csv;

执行的查询与结果 #

初始查询 #

执行以下SQL：

select id, c.actor_id, d.actor_id from community c
left join duplicate_community_rows_by_name_actor_id d on d.actor_id = c.actor_id
where c.actor_id like '%gameboy%';

得到结果（*null*代表实际数据库NULL值）：

添加d.actor_id IS NOT NULL条件后的查询 #

修改查询添加过滤条件后：

select id, c.actor_id, d.actor_id from community c
left join duplicate_community_rows_by_name_actor_id d on d.actor_id = c.actor_id
where c.actor_id like '%gameboy%' and d.actor_id IS NOT NULL;

结果仅剩1条数据：

尝试了左、右、外、内连接等所有类型，都无法得到预期结果，疑问点：为何WHERE子句会影响JOIN的最终结果？

原因解释 #

这是LEFT JOIN的核心执行逻辑导致的：

1. LEFT JOIN的执行顺序：先执行JOIN操作，保留左表（community）的所有行，右表匹配不到的行，其对应字段会用NULL填充。
2. WHERE子句的过滤时机：WHERE子句是在JOIN操作完成之后执行的。当你添加d.actor_id IS NOT NULL条件时，会把JOIN后d.actor_id为NULL的行（也就是左表中没有匹配到右表的行）全部过滤掉。
3. 等价效果：此时你的查询完全等价于INNER JOIN，因为INNER JOIN只会保留两张表都匹配成功的行，和LEFT JOIN后过滤NULL的结果一致。

如果你的预期是保留左表所有符合c.actor_id like '%gameboy%'的行，同时标记出哪些在右表中有匹配，应该把d.actor_id IS NOT NULL的条件放到JOIN的ON子句里，而非WHERE子句：

select id, c.actor_id, d.actor_id from community c
left join duplicate_community_rows_by_name_actor_id d 
  on d.actor_id = c.actor_id and d.actor_id IS NOT NULL
where c.actor_id like '%gameboy%';

这样JOIN时只会匹配右表非NULL的行，但左表所有符合WHERE条件的行都会被保留，不匹配的行d.actor_id依然为NULL。

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