Find rows in A that don't have an associated row in B, where the FK is on B?

Question

What I've been doing is

SELECT * FROM a LEFT JOIN b ON b.a_id=a.id WHERE b.id IS NULL

Basically, I'm trying to find the rows of a that don't have an associated b, where the foreign key is stored on b. Is this the proper way to do it, or there a different kind of join to do this?

Answer 1

You are looking for NOT EXISTS:

SELECT 
  * 
FROM 
  a
WHERE
  NOT EXISTS (SELECT 1 FROM b WHERE a_id = a.id)

Having an index on b.a_id helps the performance of this query.