CODEWITHSUNDEEP
mysqlselectjoinforeign-keys
Niek de Klein
Niek de Klein

Reputation: 8824

Easy way to find out if there is no foreign key link from table A to table B?

Lets say I have table A, with an id column, and table B with an A_id column. A_id is a foreign key of id. Now, if I want to get all id's from A of which B has a foreign key link, I can do

SELECT id FROM A JOIN B ON id = A_id

However, how can I select all id's from A where B does not link to? (without selecting all id's and subtracting the above subset from that)

Upvotes: 4

Views: 1001

Answers (2)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

SELECT A.id FROM
A LEFT JOIN B ON A.id = B.A_id
WHERE B.A_id IS NULL;

Upvotes: 2

Quassnoi
Quassnoi

Reputation: 425431

SELECT  id
FROM    a
WHERE   id NOT IN
        (
        SELECT  a_id
        FROM    b
        )

This will use an anti-join: for each record from a, it will search b for the record's id (using an index on b.a_id) and if none found, return the record.

Upvotes: 3

Related Questions