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')
->fields('n', array('nid'));
$subquery = db_select('node', 'n2')
->fields('n2', array('nid'))
->join('users', 'u', 'n2.uid = u.uid'
->condition('u.status', 0);
$query->condition('', $subquery, 'EXISTS');
$result = $query->execute();
You can also use db_query() like you used to do in Drupal 6 and of course SelectQuery::notExists() is available.