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

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 开发者_StackOverflow中文版stored on b. Is this the proper way to do it, or there a different kind of join to do this?

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.

yes, that is the normal way, i don't know of a better way.

You could try

Select * from a where a.id not in (Select a_id from b)

The three previous response are correct, at this point I think you can considere the performance of the three kind of query. You can take a look to this URL:

http://rockycode.com/blog/not-vs-outer-join-performance/

In my opinion I think that outer join is the best way and, if you have not too much rows, the three way are more or less equals.