How to compare two tables in Postgresql?

Question

I have two identical tables:

A :   id1, id2, qty, unit
B:    id1, id2, qty, unit

The set of (id1,id2) is identifying each row and it can appear only once in each table.

I have 140 rows in table A and 141 rows in table B. I would like to find all the keys (id1,id2) that are not appearing in both tables. There is 1 for sure but there can't be more (for example if each table has whole different data).

I wrote this query:

(TABLE a EXCEPT TABLE b)
UNION ALL
(TABLE b EXCEPT TABLE a) ;

But it's not working. It compares the whole table where I don't care if qty or unit are different, I only care about id1,id2.

Answer 1

use a full outer join:

 select a.*,b.* 
 from a full outer join b 
   on a.id1=b.id1 and a.id2=b.id2

this show both tables side by side. with gaps where there is an unmatched row.

 select a.*,b.* 
 from a full outer join b 
   on a.id1=b.id1 and a.id2=b.id2
   where a.id1 is null or b.id1 is null;

that will only show unmatched rows.

or you can use not in

select * from a 
  where (id1,id2) not in
   ( select id1,id2 from b )

that will show rows from a not matched by b.

or the same result using a join

select a.* 
  from a left outer join b 
  on a.id1=b.id1 and a.id2=b.id2
  where b.id1 is null

sometimes the join is faster than the "not in"