user3484582 user3484582 - 5 months ago 10
SQL Question

SQL: Select entries which don't have composite primary key in another table

So I have two tables which both have a composite primary key made from two columns. I want to find the entries in the first table that do NOT exist in the second table, always keeping in mind that my primary key is composited.

I know I have to use

NOT IN
but I'm not sure how to make it work with two primary keys. Essentially I want something like this:

SELECT * FROM table1
WHERE id NOT IN (SELECT id FROM table2)


The
id
though is a composited primary key made of two columns,
id1
and
id2
.
Any ideas how to approach that?

Answer

You can use a row constructor along with NOT IN:

SELECT *
FROM table1 
WHERE (id1, id2) NOT IN (SELECT id1, id2 FROM table2);