Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to compare two tables in Postgresql?

Tags:

sql

postgresql

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.

like image 651
java Avatar asked Jan 06 '16 06:01

java


People also ask

How do I find the relationship between two tables in PostgreSQL?

If there are foreign keys between the tables, then you can find the relationship between them. To do this, you can call \d on a table and see the foreign keys associated with its columns.

How do I compare two Access tables for differences?

To compare two tables by using a field as a criterion, you create a select query that includes both tables. You include the fields that you want to display, and you also include the field that corresponds to the field that you want to use as a criterion. You then create a criterion to compare the tables.


1 Answers

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"

like image 83
Jasen Avatar answered Oct 10 '22 08:10

Jasen