IN predicate

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

Read syntax diagramSkip visual syntax diagram
>>-+-expression1--+-----+--IN--+-(--fullselect1--)---------+----+-><
   |              '-NOT-'      |    .-,---------------.    |    |   
   |                           |    V                 |    |    |   
   |                           +-(------expression2---+--)-+    |   
   |                           '---expression3-------------'    |   
   '-(--row-value-expression--)--+-----+--IN--(--fullselect2--)-'   
                                 '-NOT-'                            

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.

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

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')