How do I find records that are not joined?

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

The following image will help to understand SQL LET JOIN :

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;

SELECT <columnns>
FROM a WHERE id NOT IN (SELECT a_id FROM b)

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 :)

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)

The first approach is

select a.* from a where a.id  not in (select b.ida from b)

the second approach is

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

The first approach is very expensive. The second approach is better.

With PostgreSql 9.4, I did the "explain query" function and the first query as a cost of cost=0.00..1982043603.32. Instead the join query as a cost of cost=45946.77..45946.78

For example, I search for all products that are not compatible with no vehicles. I've 100k products and more than 1m compatibilities.

select count(*) from product a left outer join compatible c on a.id=c.idprod where c.idprod is null

The join query spent about 5 seconds, instead the subquery version has never ended after 3 minutes.