How do I use "NOT IN" in a query?

Question

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->condition('n.' . $key, $value, 'not in (select language from 
  languages where language = $value)');

Answer 1

In the specific example, you should simply write the condition as:

$query->condition('n.language', 'ab', '<>');

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')->fields('n');
  $query->condition('n.nid', array(1, 2, 3), 'NOT IN');
  $nodes = $query->execute();
  
  foreach ($nodes as $node) {
    dsm($node->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->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')->extend('PagerDefault');
  $nids = $query
  ->fields('n', array('nid', 'sticky', 'created'))
    ->condition('type', 'blog')
    ->condition('status', 1)
    ->orderBy('sticky', 'DESC')
    ->orderBy('created', 'DESC')
    ->limit(variable_get('default_nodes_main', 10))
    ->addTag('node_access')
    ->execute()
    ->fetchCol();

Similar code is used by book_block_view().

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

Answer 2

When writing complex queries you should definitely use db_query() instead of db_select().

1. You can't write a NOT IN clause with a subquery with the current Drupal database API (it's a know issue being worked out).
2. If you don't need your query to be dynamic (hence rewritten by others modules), don't bother trying to write such a complex one with db_select().
3. Subqueries aren't well supported yet (see a previous answer of mine) and if you're used to write SQL it's way easier to use db_query().

Regarding your query, I'm not sure why you want to use a subquery (unless you simplified your exemple) ? You can write it easily like this:

SELECT nid 
FROM node n INNER JOIN languages l ON n.language = l.language
WHERE language NOT IN ('ab')

DISTINCT isn't necessary as nid is a primary key so it won't be duplicated.

Answer 3

There's also where() which allows to add an arbitrary where condition to the query.

Example:

$query->where('n.language NOT IN (SELECT language FROMlanguages WHERE language = :lang)', array(':lang' => $value));

As keithm mentioned, you must use db_select() and addTag('node_access') when selecting nodes which are then displayed to users.

Answer 4

An easier way to use db_select with a NOT IN subselect is just to use the little known

$query->where

to add an arbitrary where condition.

e.g:

  // Count query for users without rid 3
  $query = db_select('users', 'u');
  $query->fields('u', array('uid'));
  $query->where('u.uid NOT IN(select uid from {users_roles} where rid = :rid)', array(':rid' => 3));  
  $count = $query->countQuery()->execute()->fetchField();
  drupal_set_message($count);

Answer 5

Where $subquery_values is an array of $key => $nid format as result of a subquery

$query->condition('node.nid', array_values($subquery_values), "NOT IN");

it works fine.