"WHERE NOT IN ( SELECT... )" with SelectQuery? Is it possible to add a WHERE NOT IN (SELECT...) clause to a SelectQuery object? I was looking at SelectQuery::condition(), and it doesn't seem to allow for this syntax:

public SelectQuery::condition($field, $value = NULL, $operator = NULL)


It only seems to allow for value comparison expressions. Is there another method that provides this syntax? 

I'm looking at conditions on the order of thousands of values, so it seems inefficient to have a bunch of AND value &lt;&gt; x conditions.
What you're looking for is SelectQuery::exists(), unfortunately it doesn't seems to work with "common way" to build such clause:
Normally in a EXISTS clause you put a JOIN on tables outside of your sub query like this:

SELECT n.nid
FROM node n
WHERE EXISTS (
  SELECT NULL
  FROM users u
  WHERE u.uid = n.uid
  AND u.status = 0)


This returns the nodes of blocked users (yes it can be built easily without the EXISTS but it's for teaching purposes ;).

The problem here is that the exists condition doesn't support "outside linking", the u.uid = n.uid part so you have to build a completely independent query which is not very efficient IMHO.
In Drupal you will write this:

$query = db_select('node', 'n')
  -&gt;fields('n', array('nid'));
$subquery = db_select('node', 'n2')
  -&gt;fields('n2', array('nid'))
  -&gt;join('users', 'u', 'n2.uid = u.uid'
  -&gt;condition('u.status', 0);
$query-&gt;condition('', $subquery, 'EXISTS');
$result = $query-&gt;execute();


You can also use db_query() like you used to do in Drupal 6 and of course SelectQuery::notExists() is available.