How can I make db_select recognize an empty field? I am writing a db_select in D7 to get various user fields, including the URI's of user's avatars (if it exists).

My initial attempt looked this:

$query = db_select('node', 'n');
$query-&gt;join('users', 'u', 'n.uid = u.uid');
$query-&gt;join('file_managed', 'f',
  'f.fid = u.picture OR u.picture = :null', array(':null' =&gt; 0));
$query-&gt;fields('f',array('uri'));


This almost do the job.  The only glitch is that if the user has no avatar, the field uri always contains the uri pointing to f.fid #1.  In that case, I want it be missing or empty.

As a workaround, I now execute two db_selects: First one for all users that have an avatar:

$query-&gt;join('file_managed', 'f', f.fid = u.picture);


Then one for all users without:

$query-&gt;join('file_managed', 'f', u.picture = :null', array(':null' =&gt; 0));


I then just concatenate the results. This produces the desired result, but is a bit clumsy.

I would like to do it with one db_select if that is possible.
Just do a left join on 'files_managed' table

$query = db_select('node', 'n');
$query-&gt;join('users', 'u', 'n.uid = u.uid');
$query-&gt;leftjoin('file_managed', 'f', 'f.fid = u.picture');
$query-&gt;fields('f',array('uri'));
$query-&gt;fields('n',array('nid'));
$query = $query-&gt;execute();
dpm($query-&gt;fetchAll());