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