Adding null check to join causes non-null rows to disappear

With the following query:

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%';

I get the results (where <null> is an actual null value):

151492,https://lemm.ee/c/gameboy,<null>
627389,https://lemmy.world/c/gameboy,https://lemmy.world/c/gameboy
55519,https://lemmy.world/c/gameboy,https://lemmy.world/c/gameboy

If I modify my query to select results that do not have a null actor_id:

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;

my results are reduced by 2, to only a single element:

627389,https://lemmy.world/c/gameboy,https://lemmy.world/c/gameboy

I have tried every kind of join. Left, right, outer, inner. None of them work. What am I misunderstanding about joining here? Why is a where clause affecting the join?

The duplicate_community_rows_by_name_actor_id table is created like this:

create temporary table duplicate_community_rows_by_name_actor_id(name text, actor_id text);
select * from duplicate_community_rows_by_name_actor_id;
copy pg_temp.duplicate_community_rows_by_name_actor_id from '/var/lib/postgresql/data/duplicates.csv' delimiter ',' csv;
select * from duplicate_community_rows_by_name_actor_id where name like '%gameboy%';

This is postgres 16.

You need to sign in to view this answers