Background documentationIN Predicate (in_predicate) Locate this document in the navigation structure

 

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

  1. <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.
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)