IN predicate

The IN predicate compares a value or values with a set of values.

Read syntax diagramSkip visual syntax diagramexpression1NOTIN(fullselect1)(,expression2)expression3row-value-expressionNOTIN(fullselect2)

When expression1 is specified, the IN predicate compares a value with a set of values. When fullselect1 is specified, the fullselect must return a single result column, and can return any number of values, whether null or not null. The data type of expression1 and the data type of the result column of the fullselect1, expression2, or expression3 must be compatible. Each variable must identify a structure or variable that is described in accordance with the rule for declaring host structures or variables.

When a row-value-expression is specified, the IN predicate compares values with a collection of values.

  • SELECT * is not allowed in the outermost select list of fullselect2.
  • The result table of the fullselect2 must have the same number of columns as row-value-expression. The data types of the corresponding expressions of row-value-expression and of its the corresponding result column of fullselect2 must be compatible. The value of each expression in row-value-expression is compared with the value of its corresponding result column of fullselect2.

The value of the predicate depends on the operator that is specified:

  • When the operator is IN, the result of the predicate is:
    • True if at least one row returned from the fullselect2 is equal to the row-value-expression.
    • False if the result of fullselect2 is empty or if no row returned from the fullselect2 is equal to the row-value-expression.
    • Otherwise, unknown (that is, if the comparison of row-value-expression to the row returned from fullselect2 evaluates to unknown because of a null value for at least one row returned from fullselect2 and no row returned from fullselect2 is equal to the row-value-expression).
  • When the operator is NOT IN, the result of the predicate is:
    • True if the result of fullselect2 is empty or if the row-value-expression is not equal to any of the rows returned by fullselect2.
    • False if the row-value-expression is equal to at least one row returned by fullselect2.
    • Otherwise, unknown (that is, if the comparison of row-value-expression to the row returned from fullselect2 evaluates to unknown because of a null value for at least one row returned from fullselect2 and the comparison of row-value-expression to the row returned from fullselect2 is not true for any row returned by fullselect2).

An IN predicate is equivalent to other predicates as follows:

IN predicate Equivalent predicate
expression IN (expression) expression = expression
expression IN (fullselect) expression = ANY (fullselect)
expression NOT IN (fullselect) expression <> ALL (fullselect)
expression IN (expression1, expression2, ..., expressionn) expression IN (SELECT * FROM R)

Where T is a table with a single row and R is a temporary table formed by the following fullselect: 
   SELECT expression1 FROM T
     UNION
   SELECT expression2 FROM T
     UNION
       .
       .
       .
     UNION
   SELECT expressionn FROM T
row-value-expression IN (fullselect) row-value-expression = SOME ( fullselect)
row-value-expression IN (fullselect) row-value-expression = ANY ( fullselect)
row-value-expression NOT IN (fullselect) row-value-expression <> ALL ( fullselect)

If the operands of the IN predicate have different data types or attributes, the rules used to determine the data type for evaluation of the IN predicate are those for UNION, UNION ALL, EXCEPT, and INTERSECT. For a description, see Rules for result data types.

If the operands of the IN predicate are strings with different CCSIDs, the rules used to determine which operands are converted are those for operations that combine strings. For a description, see Conversion rules for operations that combine strings.

If the corresponding operands of the predicate are SBCS data, mixed data, or Unicode data, and if the collating sequence in effect at the time the statement is executed is not *HEX, then the comparison of the operands is performed using weighted values for the operands. The weighted values are based on the collating sequence.

Examples

   DEPTNO IN ('D01', 'B01', 'C01')
 
   EMPNO IN(SELECT EMPNO FROM EMPLOYEE WHERE WORKDEPT = 'E11')