MonetDB supportd SQL-compliant subqueries. All the expressions discussed here return a BOOLEAN.
Note: avoid expressions with side effects, e.g. function calls that modify the database or emits output.
EXISTS '(' subquery ')'
The argument to exists is a Table Expression which returns a collection of rows. The function returns True if the result set of the table expression contains at least one row. In MonetDB the subquery is currently completely evaluated.
The subquery should produce a table with a single column. The left-hand 'expression' is evaluated and compared to each row of the subquery result using the given operator, which must yield a Boolean result. The result of IN is "true" if any equal subquery row is found. The result is "false" if no equal row is found (including the case where the subquery returns no rows).
expression [NOT] IN '(' subquery ')'
The NOT negates the result.
expression operator ANY '(' subquerry ')'
expression operator SOME '(' subquery ')'
The result of ANY is "true" if any true result is obtained. The result is "false" if no true result is found (including the case where the subquery returns no rows).
SOME is a synonym for ANY. IN is equivalent to = ANY.
expression operator ALL '(' subquery ')'
The result of ALL is "true" if all rows yield true (including the case where the subquery returns no rows). The result is "false" if any false result is found. The result is NULL if the comparison does not return false for any row, and it returns NULL for at least one row.
NOT IN is equivalent to <> ALL.