Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
--`join using` makes things very short
with result1(src__id,period,amount_a) as (values ('id1','Q1',2) ),
     result2(src__id,period,amount_b) as (values ('id1','Q1',9),
                                                 ('id2','Q2',1) ),
     result3(src__id,period,amount_c) as (values ('id2','Q2',4))
select *
from result1 
full outer join result2 using (src__id,period)
full outer join result3 using (src__id,period);


src__id
period
amount_a
amount_b
amount_c


id1
Q1
2
9
null

id2
Q2
null
1
4



SELECT 2


--`on` brings over all matching columns for you to resolve
with result1(src__id,period,amount_a) as (values ('id1','Q1',2) ),
     result2(src__id,period,amount_b) as (values ('id1','Q1',9),
                                                 ('id2','Q2',1) ),
     result3(src__id,period,amount_c) as (values ('id2','Q2',4))
select *
from result1 
full outer join result2 on result1.src__id=result2.src__id 
                       and result1.period =result2.period 
full outer join result3 on result1.src__id=result3.src__id 
                       and result1.period =result3.period;


src__id
period
amount_a
src__id
period
amount_b
src__id
period
amount_c


id1
Q1
2
id1
Q1
9
null
null
null

null
null
null
id2
Q2
1
null
null
null

null
null
null
null
null
null
id2
Q2
4



SELECT 3


--resolving matching columns resulting from the `join on` before aggregation
with result1(src__id,period,amount_a) as (values ('id1','Q1',2) ),
     result2(src__id,period,amount_b) as (values ('id1','Q1',9),
                                                 ('id2','Q2',1) ),
     result3(src__id,period,amount_c) as (values ('id2','Q2',4))
select coalesce(result1.src__id,
                result2.src__id,
                result3.src__id) as src__id_o,
       coalesce(result1.period,
                result2.period,
                result3.period) as period_o,
       amount_a,
       amount_b,
       amount_c
from result1 
full outer join result2 on result1.src__id=result2.src__id 
                       and result1.period =result2.period 
full outer join result3 on result1.src__id=result3.src__id 
                       and result1.period =result3.period;


src__id_o
period_o
amount_a
amount_b
amount_c


id1
Q1
2
9
null

id2
Q2
null
1
null

id2
Q2
null
null
4



SELECT 3


--resolving matching columns resulting from the `join on` after aggregation
--it's over twice as long as the initial approach with a `join using`
with result1(src__id,period,amount_a) as (values ('id1','Q1',2) ),
     result2(src__id,period,amount_b) as (values ('id1','Q1',9),
                                                 ('id2','Q2',1) ),
     result3(src__id,period,amount_c) as (values ('id2','Q2',4))
select coalesce(result1.src__id,
                result2.src__id,
                result3.src__id) as src__id_o,
       coalesce(result1.period,
                result2.period,
                result3.period) as period_o,
       sum(amount_a) as amount_a,
       sum(amount_b) as amount_b,
       sum(amount_c) as amount_c
from result1 
full outer join result2 on result1.src__id=result2.src__id 
                       and result1.period =result2.period 
full outer join result3 on result1.src__id=result3.src__id 
                       and result1.period =result3.period
group by src__id_o,period_o;


src__id_o
period_o
amount_a
amount_b
amount_c


id1
Q1
2
9
null

id2
Q2
null
1
4



SELECT 2
src__id	period	amount_a	src__id	period	amount_b	src__id	period	amount_c
id1	Q1	2	id1	Q1	9	null	null	null
null	null	null	id2	Q2	1	null	null	null
null	null	null	null	null	null	id2	Q2	4