The IN predicate (in_predicate) is a predicate that 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>,...)
SQL Tutorial, Conditions: Comparison, AND, OR, BETWEEN, IN, Negative Conditions: NOT, Subquery: Inner Queries
The subquery (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 with each other.
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)