IN Predicate (in_predicate)

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.

Structure

Syntax Syntax

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

End of the code.

Examples

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

Explanation

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)