sql query to calculate a difference between cols in two different tables, grouped by common identifiers on the row

Question

This is probably an amazingly dumb question, but hopefully someone kind can help out while laughing :D I'm doing this on postgres 9.5

I have two tables, all data types can int in this example table1 has:

id     val

1      10

2      20

3      30

table2 has:

id     value

1      15

3      15

5      50

I would very much like a query that calculates the diff between the two tables (table2.val - table1.val) for each id, so that the result is:

id     diff

1      5

2      -20

3      -15

5      50

My difficulty is in figuring out how to do it when some ID's only exist in one table...

Please help :)

So to create my best attempt:

create table table1 (id int, value int);
create table table2 (id int, value int);
insert into table1 values 
(1,10),
(2,20),
(3,30);
insert into table2 values 
(1,15),
(3,15),
(5,50);


select table1.id, table2.value - table1.value as diff
from table1
inner join table2 on table1.id=table2.id
group by table1.id, diff;

However this leads to :

id      diff
3       -15
1       5

If I try a full outer join I get:

select table1.id, table2.value - table1.value as diff
from table1
full outer join table2 on table1.id=table2.id
group by table1.id, diff;

which gives

id      diff
null    null
2       null
3       -15
1       5

Answer 1

To get results even if only one of the two tables has data, then you need FULL JOIN. The COALESCE() function can be used for the result when there are umatched rows (and you want the produced NULL to become 0 for example).

Assuming that (id) is UNIQUE in each table, I don't see why you think you need GROUP BY at all.

The query:

select
    coalesce(t1.id, t2.id) as id,
    coalesce(t2.value, 0) - coalesce(t1.value, 0) as diff
from table1 as t1
     full join table2 as t2
     on t1.id = t2.id ;

and slightly simpler, with USING:

select
    id,
    coalesce(t2.value, 0) - coalesce(t1.value, 0) as diff
from table1 as t1
     full join table2 as t2
     using (id) ;

Test at dbfiddle.uk.