>>-+-expression--+-----+--IN--+-(--fullselect--)---------+-----+->< | '-NOT-' | .-,--------------. | | | | V | | | | +-(------expression---+--)-+ | | '---expression-------------' | '-(--row-value-expression--)--+-----+--IN--(--fullselect--)-' '-NOT-'
The IN predicate compares a value or values with a set of values.
When a single expression is specified on the left side of the operator, the IN predicate compares a value with a set of values. When a fullselect 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 expression and the data type of the result column of the fullselect or the expression on the right side of the operator 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:
If the corresponding operands of the predicate are SBCS data, mixed data, or Unicode data, and if the sort 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 sort sequence.
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 (value1, value2, ..., valuen) | 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 value1 FROM T UNION SELECT value2 FROM T UNION . . . UNION SELECT valuen 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.
DEPTNO IN ('D01', 'B01', 'C01') EMPNO IN(SELECT EMPNO FROM EMPLOYEE WHERE WORKDEPT = 'E11')
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.