Search code examples
sqlpostgresqlcomposite-keysql-except

Using SQL EXCEPT just by primary key

I have 2 tables (A and B) in my database and I need to unite both. Both the tables have composites keys (nine columns) as primary key and there are duplicate rows among the tables.

I could use 'EXCEPT' to get the rows present in result of the SELECT A but not in result of the SELECT B, but further I need retrieve others columns that together don't duplicate rows. I.e.

SELECT ckA1, ckA2, ... ckA9, columnA1, columnA2, ... columnAN
FROM A
EXCEPT
SELECT ckB1, ckB2, ... ckB9, columnB1, columnB2, ... columnBN
FROM B

wherein ckA1 = ckB1, ckA2 = ckB2, ... ckA9 = ckB9, but columnA1 isn't necessarily equal to columnB1, columnA2 isn't necessarily equal to columnB2, etc.

Is there a way to resolve this?

Solution

  • Use NOT EXISTS operator and correlated subquery:

    SELECT ckA1, ckA2, ... ckA9, columnA1, columnA2, ... columnAN
FROM A
WHERE NOT EXISTS (
  SELECT 1 FROM B
  WHERE ckA1 = ckB1, ckA2 = ckB2, ... ckA9 = ckB9
)