Firebird 2.0
PSQL Reference
Manual

IN CONDITION

Pupose

Use the IN predicate to test if a value is within a group of values. The group can either be a list of values or the result set of a single column query.

Syntax

<in_predicate> ::=
     <psql_expr> [NOT] IN ( psql_expr_list )
   | <psql_expr> [NOT] IN ( select_stmt )


Semantics

The IN predicate tests for membership of a value in a list of values or a result set of values:

  • IN is equivalent with the = ANY group comaprison

  • NOT in is equivalent with != ALL group comparison

If any item in the list following a NOT IN operation evaluates to null, then the entire condition evaluates to NULL. This is because the condition is internally calculated as a series of AND conditions, which cannot be short-cut evaluated.

Examples

The following are valid examples of IN usage:

   disciple IN ('John', 'Paul')
   rank NOT IN (1, 2, 3)


The following statement returns no rows:

SELECT ’True’ FROM employees
WHERE department_id NOT IN (10, 20, NULL);


The preceding example returns no rows because the WHERE clause condition evaluates to: department_id != 10 AND department_id != 20 AND department_id != NULL. Because the third condition compares department_id with a NULL, it results in a NULL, so the entire expression results in NULL. This behavior can easily be overlooked, especially when the NOT IN operator references a subquery.

See also

Group Comparison Conditions


previous page goto index next page

Legal information