sqljoinrdbms-agnostic

SQL join problem

I want to retrieve all records from one table when there are no matches in the second table.

So it is kind of the opposite of an inner join.

Solution

  • You need a LEFT JOIN WHERE IS NULL query (aka outer join):

    SELECT table1.*
FROM table1
LEFT OUTER JOIN table2 
   ON table1.id = table2.id
WHERE table2.id IS NULL

    Or a NOT IN:

    SELECT *
FROM table1
WHERE id NOT IN (SELECT id FROM table2)