2

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
4
  • 4
    Look at FULL OUTER JOIN, and COALESCE (to handle the NULL values where a row only exists in one table).
    – RDFozz
    May 3, 2018 at 22:15
  • Sounds good: but I can't quite wrap my head around how to do the outerjoin with coalesce: do you have some idea on how the query looks?
    – BurnedOut
    May 4, 2018 at 17:45
  • In questions like this, we do usually request that the asker post their attempt at the query, and let us know what problem they're seeing.
    – RDFozz
    May 4, 2018 at 17:56
  • Updated the original post, hope this illustrates my difficulty...
    – BurnedOut
    May 4, 2018 at 18:34

1 Answer 1

3

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.

1
  • Awesome! Thanks ypercube. Yep, the group by is a result of muddied thinking since the actual queries are much larger and complex, I was trying to simplyfy them as much as possible. Thanks again!
    – BurnedOut
    May 4, 2018 at 18:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.