IN predicate

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


>>-+-expression1--+-----+--IN-+-(fullselect1)-----------+-+----><
   |              '-NOT-'     |   .-,---------------.   | |   
   |                          |   V                 |   | |   
   |                          '-(-----expression2---+-)-' |   
   '-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 fullselect1 or expression2 must be compatible. If expression is a single host variable, the host variable can identify a structure. Any host variable or structure that is specified must be described in the application program according to the rules for declaring host structures and variables.

When a row-value-expression is specified, the IN predicate compares values with a collection of values. The result table of the fullselect2 must have the same number of columns as the row-value-expression. The data type of each expression in row-value-expression and the data type 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 fullselect2 is equal to the row-value-expression.
- False if the result of fullselect2 is empty or if no row returned from 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 byfullselect2.

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 the fullselect2).

The IN predicate is equivalent to the quantified predicate as follows:

Table 1. IN predicate and equivalent quantified predicates
IN predicate	Equivalent quantified predicate
`expression1 IN (expression2)`	`expression1 = expression2`
`expression IN (fullselect1)`	`expression = ANY (fullselect1)`
`expression NOT IN (fullselect1)`	`expression <> ALL (fullselect1)`
`expression1 IN (expressiona, expressionb, ...)`	`expression1 IN ( SELECT * FROM R)` When T is a table with a single row and R is a result 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 (fullselect2)`	`row-value-expression = SOME (fullselect2)`
`row-value-expression IN (fullselect2)`	`row-value-expression = ANY (fullselect2)`
`row-value-expression NOT IN (fullselect2)`	`row-value-expression <> ALL (fullselect2)`

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

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. See Character and graphic string comparisons.

Example 1: The following predicate is true for any row whose employee is in department D11, B01, or C01.

  WORKDEPT IN ('D11', 'B01', 'C01')

Example 2: The following predicate is true for any row whose employee works in department E11.

  EMPNO IN (SELECT EMPNO FROM DSN8A10.EMP
    WHERE WORKDEPT = 'E11')

Example 3: The following predicate is true if the date that a project is estimated to start (PRENDATE) is within the next two years.

  YEAR(PRENDATE)  IN (YEAR(CURRENT DATE),
                      YEAR(CURRENT DATE + 1 YEAR),
                      YEAR(CURRENT DATE + 2 YEARS))

Example 4: The following example obtains the phone number of an employee in DSN8A10.EMP where the employee number (EMPNO) is a value specified within the COBOL structure defined below.

  77 PHNUM  PIC X(6).
  01 EMPNO-STRUCTURE.
     05 CHAR-ELEMENT-1  PIC X(6) VALUE '000140'.
     05 CHAR-ELEMENT-2  PIC X(6) VALUE '000340'.
     05 CHAR-ELEMENT-3  PIC X(6) VALUE '000220'.
     .
     .
     .
  EXEC SQL DECLARE PHCURS CURSOR FOR
       SELECT PHONENO FROM DSN8A10.EMP
         WHERE EMPNO IN 
         (:EMPNO-STRUCTURE.CHAR-ELEMENT-1,
          :EMPNO-STRUCTURE.CHAR-ELEMENT-2,
          :EMPNO-STRUCTURE.CHAR-ELEMENT-3)
  END-EXEC.
  EXEC SQL OPEN PHCURS
  END-EXEC.
  EXEC SQL FETCH PHCURS INTO :PHNUM
  END-EXEC.