SQL select rows that dont have a join pair

Question

How can i do something like an opposite of join? For example from those two tables select values from table alice that are not in table bob:

alice:
id|name
--+----
1 |one
2 |two
3 |three
6 |six
7 |seven

bob:
id|a_id
--+----
15|1
16|2
17|3

to get this:

result:
name
----
six 
seven

Answer 1

This is called an anti-join.

The general idea is to do a left, right, or full outer join, and filter to find only rows where the outer side is null.

For your example case, that'd be a left anti semi join:

select a.id, a.name
from alice a
left outer join bob b on (a.id = b.a_id)
where b.id is null;

but it's also possible to find mismatches on both sides with a full outer join:

select a.id, a.name
from alice a
full outer join bob b on (a.id = b.a_id)
where b.id is null
   or a.id is null;

For the left anti join approach, you can instead use not exists:

select a.id, a.name
from alice a
where not exists (select 1 from bob b where b.a_id = a.id);

though in practice PostgreSQL will transform this into join form anyway.

It's possible to use not in instead:

select a.id, a.name
from alice a
where a.id not in (select b.a_id from bob);

but:

• You have to make sure there can be no nulls in the subquery result, because 1 not in (2, null) is null not true;

• It can be much less efficient

so in general using an anti-join or exists subquery is strongly preferred.