IN predicate 

The IN predicate (in_predicate) checks whether a value or a value list is contained in a specified set of values or value lists.

Syntax

<in_predicate> ::=
  <expression> [NOT] IN <subquery>
| <expression> [NOT] IN <expression_list>
| <expression_list> [NOT] IN <subquery>
| <expression_list> [NOT] IN (<expression_list>,...)

Examples

SQL Tutorial, Conditions: Comparison, AND, OR, BETWEEN, IN, Negative Conditions: NOT, Subquery: Inner Queries

Explanation

The subquery must supply a result table (see result table name) that contains the same number of columns as the number of values specified by the expression on the left-hand side of the IN operator.

Each value list specified on the right-hand side of the IN operator must contain the same number of values as specified in the value list on the left-hand side of the IN operator.

·        x [NOT] IN S, whereby x <expression> and S <subquery> or <expression_list>
The value x and the values in S must be comparable.

·        x [NOT] IN S, whereby x <expression_list> with the values x1, x2, ..., xn and S <subquery> (set of value lists s) or (<expression_list>,...) (Range of values lists s) with the value lists s: s1, s2, ..., sn
A value xm must be comparable with all values sm.
x=s is true if xm=sm, m=1,...,n
x=s is false if there is at least one m for which xm=sm is false
x=s is undefined if there is no m for which xm=sm is false and there is at least one m for which xm=sm is undefined.

The entry '------' in the list below means that no statement can be made if only the result of the comparison with one s is known.

	Result of the function x IN S
x=s is true for at least one s	true
x=s is true for all s	true
S contains NULL values and x=s is true for the remaining s	true
S is empty	false
x=s is false for at least one s	------
x=s is false for all s	false
S contains NULL values and x=s is false for the remaining s	undefined
x=s is not true for any s and is undefined for at least one value s	undefined

x NOT IN S has the same result as NOT(x IN S)