How do I use "NOT IN" in a query? What is the proper way to write a query containing 'NOT IN' using a condition statement?

My query is the following one:

SELECT DISTINCT nid FROM node WHERE language NOT IN 
  (SELECT language 
    FROM languages WHERE language = 'ab');


I've tried something like the following:

$query-&gt;condition('n.' . $key, $value, 'not in (select language from 
  languages where language = $value)');
In the specific example, you should simply write the condition as:

$query-&gt;condition('n.language', 'ab', '&lt;&gt;');


In the generic case, where you need to select the rows in a database basing on the values returned from a sub-query, you should consider what follows:


"NOT IN" is accepted as operator from SelectQuery::condition(). In fact, the following query would be executed:

$query = db_select('node', 'n')-&gt;fields('n');
$query-&gt;condition('n.nid', array(1, 2, 3), 'NOT IN');
$nodes = $query-&gt;execute();

foreach ($nodes as $node) {
  dsm($node-&gt;nid);
}

As reported in Conditional clauses ("Subselects"), SelectQuery::condition() accepts  also an object implementing SelectQueryInterface as value for $value, such as the one returned by db_select(); the problem is that actually you can just use it when the value of $operator is equal to "IN". See Subselects don't work in DBTNG conditions, except when used as value for IN.


The only way I can see to use the "NOT IN" operator with a sub-query in condition is to:


Execute the subquery to get an array
Execute the main query setting the condition as in the following snippet

$query-&gt;condition($key, $subquery_result, 'NOT IN');


$subquery_result is the array containing the result of the sub-query.


Otherwise, you could use where() as others said, which accepts a string for the part of the query you need to add.

Keep in mind that db_select() is slower that db_query(); you should use the first when you know the query could be altered by other modules. Otherwise, if other modules are not supposed to use hook_query_alter() to alter your query, you should use db_query().
In the case of accessing nodes, if you need to obtain only the nodes to which a user has access, then you need to use db_select() and add 'node_access' as tag of the query, with SelectQuery::addTag(). For example, blog_page_last() uses the following code.

  $query = db_select('node', 'n')-&gt;extend('PagerDefault');
  $nids = $query
  -&gt;fields('n', array('nid', 'sticky', 'created'))
    -&gt;condition('type', 'blog')
    -&gt;condition('status', 1)
    -&gt;orderBy('sticky', 'DESC')
    -&gt;orderBy('created', 'DESC')
    -&gt;limit(variable_get('default_nodes_main', 10))
    -&gt;addTag('node_access')
    -&gt;execute()
    -&gt;fetchCol();


Similar code is used by book_block_view().

$select = db_select('node', 'n')
  -&gt;fields('n', array('title'))
  -&gt;condition('n.nid', $node-&gt;book['bid'])
  -&gt;addTag('node_access');
$title = $select-&gt;execute()-&gt;fetchField();