Comparing records of tables based on composite key

Question

I have a job to refresh a look up table data for which I need help. I have 2 tables with data, one is the existing version (table1) and the other is the new version (table2). The primary key for each of the tables is a composite key. I know there is a different number of rows in each one and I need to find out the differences. Let's assume that the 2 columns part of the composite primary key to be named column1 and column2.

Typically, in the "normal" primary key set-up, I would just look for primary key values NOT IN the list of primary keys from the other table. But I don't know how to do this with a composite primary key.

I found a similar thread for SQL Server but that doesn't seem to work in my case, not sure if I'm doing something wrong.

How can I compare rows from 2 tables that have composite primary keys?

Can someone help me compare the rows from these two tables in Oracle/PostgreSQL?

`SELECT ... FROM a WHERE NOT EXISTS (SELECT * FROM b WHERE b.x = a.x AND b.y = a.y);` BTW: this hat nothing to do with primary keys; it works for non-key columns just as well. — wildplasser, Mar 04 '15 at 15:39
The `FULL JOIN` query will work in both Postgres and Oracle. — ypercubeᵀᴹ, Mar 04 '15 at 15:39
What do you want as output? All columns from the tables? Or just the PK values? — ypercubeᵀᴹ, Mar 04 '15 at 15:40
@wildplasser: Thank you for the solution. The answer worked !! — user4104265, Mar 04 '15 at 15:47
@ypercube: Thank you for taking time to help on this request. I jsut needed the Primary key values and I was able to get the result from the previous answer. — user4104265, Mar 04 '15 at 15:48

score 3 · Answer 1 · answered Mar 04 '15 at 15:48

3

SELECT ...
FROM a
WHERE NOT EXISTS (
  SELECT *
  FROM b
  WHERE b.x = a.x AND b.y = a.y
  );

BTW: this hat nothing to do with primary keys; it works for non-key columns just as well.

answered Mar 04 '15 at 15:48

wildplasser

41,380
7
58
102

score 0 · Answer 2 · edited May 23 '17 at 12:26

0

You could even use NOT IN with composite values. Example:

How do I (or can I) SELECT DISTINCT on multiple columns?

But other syntax variants are typically simpler / faster / more reliable:

Select rows which are not present in other table

NOT EXISTS like @wildplasser posted is a good candidate for best performance.

edited May 23 '17 at 12:26

Community

1
1

answered Mar 04 '15 at 15:50

Erwin Brandstetter

539,169
125
977
1,137

Comparing records of tables based on composite key

2 Answers2