By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table df_1 (job_id int, worker_id int, random_id text);
insert into df_1 (job_id, worker_id, random_id)
values
(1711418, 45430, 'random'),
(1711418, 46353, 'random'),
(1711418, 45158, 'random');
3 rows affected
create table df_2 (job_id int, worker_id int);
insert into df_2 (job_id, worker_id)
values
(1711418, 45430),
(1711419, 46117),
(1711419, 46322),
(1711419, 45926);
4 rows affected
select job_id, worker_id, random_id
from df_1
union all
select job_id, worker_id, 'NaN'
from df_2 d2
where not exists (select *
from df_1 d1
where d1.job_id = d2.job_id
and d1.worker_id = d2.worker_id);
| job_id | worker_id | random_id |
|---|---|---|
| 1711418 | 45430 | random |
| 1711418 | 46353 | random |
| 1711418 | 45158 | random |
| 1711419 | 46117 | NaN |
| 1711419 | 46322 | NaN |
| 1711419 | 45926 | NaN |
select job_id, worker_id, random_id
from df_1
union all
select job_id, worker_id, 'NaN'
from
(
select job_id, worker_id
from df_2 d2
except
select job_id, worker_id
from df_1
) t;
| job_id | worker_id | random_id |
|---|---|---|
| 1711418 | 45430 | random |
| 1711418 | 46353 | random |
| 1711418 | 45158 | random |
| 1711419 | 46322 | NaN |
| 1711419 | 46117 | NaN |
| 1711419 | 45926 | NaN |