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 * from a where id not in (select a_id from b)

Or like some other people on this thread says:

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

Answer 2

select * from a where id not in (select a_id from b)

Answer 3

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

Answer 4

Another approach:

select * from a where not exists (select * from b where b.a_id = a.id)

The "exists" approach is useful if there is some other "where" clause you need to attach to the inner query.

Answer 5

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;

Answer 6

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 7

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)