How do I find records that are not joined?

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?

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

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

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.

SELECT id FROM a
EXCEPT
SELECT a_id FROM b;

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;