This is probably an amazingly dumb question, but hopefully someone kind can help out while laughing 😀
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
Best Answer
To get results even if only one of the two tables has data, then you need
FULL JOIN
. TheCOALESCE()
function can be used for the result when there are umatched rows (and you want the producedNULL
to become0
for example).Assuming that
(id)
isUNIQUE
in each table, I don't see why you think you needGROUP BY
at all.The query:
and slightly simpler, with
USING
:Test at dbfiddle.uk.