SQL Reference

IN Predicate

>>-+-expression1--+------+--IN----+-(fullselect1)----------------+-+->
   |              '-NOT--'        |    .-,----------------.      | |
   |                              |    V                  |      | |
   |                              +-(------expression2----+---)--+ |
   |                              '-expression2------------------' |
   |    .-,----------------.                                       |
   |    V                  |                                       |
   '-(------expression3----+---)----+------+--IN----(fullselect2)--'
                                    '-NOT--'
 
>--------------------------------------------------------------><
 

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

The fullselect must identify a number of columns that is the same as the number of expressions specified to the left of the IN keyword (SQLSTATE 428C4). The fullselect may return any number of rows.

• An IN predicate of the form:

     expression IN expression
  

  is equivalent to a basic predicate of the form:

     expression = expression
  

• An IN predicate of the form:

     expression IN (fullselect)
  

  is equivalent to a quantified predicate of the form:

     expression = ANY (fullselect)
  

• An IN predicate of the form:

    expression NOT IN (fullselect)
  

  is equivalent to a quantified predicate of the form:

    expression <> ALL (fullselect)
  

• An IN predicate of the form:

    expression IN (expressiona, expressionb, ..., expressionk)
  

  is equivalent to:

    expression = ANY (fullselect)
  

  where fullselect in the values-clause form is:

    VALUES (expressiona), (expressionb), ..., (expressionk)
  

• An IN predicate of the form:

    (expressiona, expressionb,..., expressionk) IN (fullselect)
  

  is equivalent to a quantified predicate of the form:

    (expressiona, expressionb,..., expressionk) = ANY (fullselect)
  

The values for expression1 and expression2 or the column of fullselect1 in the IN predicate must be compatible. Each expression3 value and its corresponding column of fullselect2 in the IN predicate must be compatible. The Rules for Result Data Types can be used to determine the attributes of the result used in the comparison.

The values for the expressions in the IN predicate (including corresponding columns of a fullselect) can have different code pages. If a conversion is necessary then the code page is determined by applying Rules for String Conversions to the IN list first and then to the predicate using the derived code page for the IN list as the second operand.

Examples:

Example 1: The following evaluates to true if the value in the row under evaluation in the DEPTNO column contains D01, B01, or C01:

  DEPTNO IN ('D01', 'B01', 'C01')

Example 2: The following evaluates to true only if the EMPNO (employee number) on the left side matches the EMPNO of an employee in department E11:

  EMPNO IN (SELECT EMPNO FROM EMPLOYEE WHERE WORKDEPT = 'E11')

Example 3: Given the following information, this example evaluates to true if the specific value in the row of the COL_1 column matches any of the values in the list:

Table 13. IN Predicate example

Expressions	Type	Code Page
COL_1	column	850
HV_2	host variable	437
HV_3	host variable	437
CON_1	constant	850

When evaluating the predicate:

  COL_1 IN (:HV_2, :HV_3, CON_4)

The two host variables will be converted to code page 850 based on the Rules for String Conversions.

Example 4: The following evaluates to true if the specified year in EMENDATE (the date an employee activity on a project ended) matches any of the values specified in the list (the current year or the two previous years):

  YEAR(EMENDATE) IN (YEAR(CURRENT DATE),
                     YEAR(CURRENT DATE - 1 YEAR),
                     YEAR(CURRENT DATE - 2 YEARS))

Example 5: The following evaluates to true if both ID and DEPT on the left side match MANAGER and DEPTNUMB respectively for any row of the ORG table.

(ID, DEPT) IN (SELECT MANAGER, DEPTNUMB FROM ORG)