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
FULL OUTER JOIN
, andCOALESCE
(to handle the NULL values where a row only exists in one table).