How to use NOT EXISTS condition? I have 2 tables:

|users |       |users_roles|
--------       -------------
| uid  |       |uid| rid   |
--------       -------------
|  1   |       | 1 |   3   |
|  2   |   


For the authenticated users the role is missing in users_roles table. User with uid=1 is an admin. I want to write the following query in Drupal DB API:

SELECT
        uid
FROM
        users
WHERE
        NOT EXISTS(
SELECT
        NULL
FROM
        users_roles
WHERE
        users_roles.uid=users.uid
)


I tried with this code, but don't know, how to write the query for notExists condition.

$query = db_select('users');

$query-&gt;addField('users', 'uid');

$query_exists = db_select('users_roles');
$query_exists-&gt;addExpression('NULL');
$query_exists-&gt;condition("users_roles.uid=users.uid"); //&lt;- how to do this. Doesn't work.

$query-&gt;notExists($query_exists);
$result = $query-&gt;execute();
foreach ($result as $record) {
        var_dump($result);
}
Why not use a simple db_query()? In many cases, this is a perfectly adequate way of doing things; you can still use named parameters, and you can build your query as you'd like. It actually performs (very) slightly better than a db_select() anyways (see: http://drupal.org/node/1067802 and https://drupal.stackexchange.com/a/1205/26).

The only reason to not use a db_query() for a particular select query is if you need the query to be easily extended or changed by some other module.

$result = db_query('SELECT uid
  FROM users
  WHERE NOT EXISTS(SELECT NULL
    FROM users_roles
    WHERE users_roles.uid = users.uid
  )')-&gt;fetchCol();


Anyways, your particular query is a little more complex than normal, but can still be done using D7's DB API. See:


http://drupal.org/node/1001242#comment-3921430
Database query with notExists ($query-&gt;condition('', $subquery, 'EXISTS');)