"WHERE NOT IN ( SELECT... )" with SelectQuery?
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.