How do I find records that are not joined?

Question

I have two tables that are joined together.

A has many B

Normally you would do:

select * from a,b where b.a_id = a.id

To get all of the records from a that has a record in b.

How do I get just the records in a that does not have anything in b?

Answer 1

SELECT id FROM a
EXCEPT
SELECT a_id FROM b;

Answer 2

This will protect you from nulls in the IN clause, which can cause unexpected behavior.

select * from a where id not in (select [a id] from b where [a id] is not null)

Answer 3

Another way of writing it

select a.*
from a 
left outer join b
on a.id = b.id
where b.id is null

Ouch, beaten by Nathan :)

Answer 4

You will probably get a lot better performance (than using 'not in') if you use an outer join:

select * from a left outer join b on a.id = b.a_id where b.a_id is null;