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

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();

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.

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.