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

foreign-keys, join, mysql, select

Solution

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.

Problem

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)

Original source