CODEWITHSUNDEEP
sqlpostgresql
java
java

Reputation: 1214

How to compare two tables in 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.

Upvotes: 10

Views: 35012

Answers (3)

DickV
DickV

Reputation: 1

select max(table) AS table, id1, id2
from (
   select 'table A' AS table, id1, id2
   from A
   union all --union would work as well, since id1,id2 is a unique combination
   select 'table B', id1, id2
   from B
) AS t
group by id1, id2
having count(*)=1

-- the max(table) shows the table that has the id-pair that doesn't exist in -- the other table

Upvotes: 0

Julie
Julie

Reputation: 51

Here is an example of using EXCEPT to see what records are different. Reverse the select statements to see what is different. a except s / then s except a

SELECT
a.address_entrytype,
a.address_street,
a.address_city,
a.address_state,
a.address_postal_code,
a.company_id


FROM
prospects.address a  

except 

SELECT
s.address_entrytype,
s.address_street,
s.address_city,
s.address_state,
s.address_postal_code,
s.company_id

FROM
prospects.address_short s

Upvotes: 5

Jasen
Jasen

Reputation: 12412

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"

Upvotes: 18

Related Questions