CODEWITHSUNDEEP
mysql
user3484582
user3484582

Reputation: 557

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?

EDIT: Taking into consideration the dangers of NOT IN I'll try to better describe what I need. Apart from selecting the entries not in the secondtable there are two more actions needed to be done in the same query:

Upvotes: 3

Views: 3077

Answers (2)

underscore_d
underscore_d

Reputation: 6797

not in seems like it would be overcomplicating things - and, as per Drew's comment, potentially very dangerous and/or infuriating if anything within the parentheses can be null.

This seems like a textbook use of an outer join. Use a left [outer] join on the key's columns, and if they (or any other single non-nullable column in the right-hand table) are found to be null after the join, that means there's no matching record having that composite key in the right-hand table.

select *
from
    t1
    inner join t3 on
        t1.whatever = t3.whatever
    left join t2 on
        t1.id1 = t2.id1 and
        t1.id2 = t2.id2
where
    t1.some_column < 5 and -- 'normal' where criteria
    t2.id1 is null;         -- no match for key in t2

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1270583

You can use a row constructor along with NOT IN:

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

Upvotes: 4

Related Questions