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.

Jasen · Accepted Answer · 2016-01-06 07:00:19Z

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"

what if row appears in b but not in a? What is the purpose of NULLs in the 2nd query?
the fist two list resuls from both tables, the second two onlt from table a, you can use union to include the complementart query to list the results from table b. the is nulls exclude the rows that contain a record from both tables.
What if rows coexist in two tables, but have different values on some columns?
the orifginal question was looking only at id1,id2 as the primary key on both tables.
@PawełSopel but iy you want to compare whole rows use a=b instead of a.id=b.id, but something different using EXCEPT might be better there.

Julie · Accepted Answer · 2022-05-25 18:23:08Z

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

Fedor · Accepted Answer · 2024-08-30 15:37:30Z

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

Collectives™ on Stack Overflow

How to compare two tables in Postgresql?

3 Answers 3

5 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Related