1

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?

Community
  • 1
  • 1
user4104265
  • 47
  • 3
  • 9
  • `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
  • @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

2 Answers2

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.

wildplasser
  • 43,142
  • 8
  • 66
  • 109
0

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

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

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

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228