How to do a db api join on "has no value"?

Question

I've got two tables, Foo and Bar. Foo and Bar can be joined by matching Foo's "nid" column to Bar's "col" column. In use, some rows in Foo will have a corresponding entry in Bar, but some won't. I now want to get all the rows in Foo that do NOT have a related entry in Bar.

I'm trying something like this:

$the_query = db_select('foo', 'f');
$the_query->fields('f', array('nid', 'val'));
$the_query->join('bar', 'b', 'f.nid = b.col');
$the_query->isNull('b.col');
$the_query->execute()...

but it's not returning anything; my method of testing for null-ness is almost certainly wrong. I've never been good at these kinds of queries, let alone being able to figure out how to do it within the context of the DB api. Any clues out there? Thanks!

Answer 1

If I am reading this right, I think you need to use a left join and then test the nullness.

$the_query = db_select('foo', 'f');
  ->fields('f', array('nid', 'val'))

$the_query->leftJoin('bar', 'b', 'f.nid = b.col');

$results = $the_query->isNull('b.col');
  ->execute()
  ->fetchAll();

Very often, though, I will break down and do this with two queries. The first will get a list of nids, the second will use a NOT IN condition.