sqlpostgresqljoin

SQL select rows that dont have a join pair

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
Solution

  • The first instinct is to use subquery, combined with NOT IN:

    SELECT name 
  FROM alice 
 WHERE id NOT IN (SELECT a_id 
                    FROM bob);

    However, a little more efficient way is to use LEFT JOIN:

    SELECT a.name
  FROM alice a
       LEFT JOIN bob b
            ON a.id = b.a_id
 WHERE b.a_id IS NULL;