# IN predicate

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:

is equivalent to a basic predicate of the form:`expression`

**IN**expression`expression = expression`

- An IN predicate of the form:

is equivalent to a quantified predicate of the form:`expression`

**IN**(fullselect)`expression =`

**ANY**(fullselect) - An IN predicate of the form:

is equivalent to a quantified predicate of the form:`expression`

**NOT IN**(fullselect)`expression <>`

**ALL**(fullselect) - An IN predicate of the form:

is equivalent to:`expression`

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

where fullselect in the values-clause form is:`expression =`

**ANY**(fullselect)**VALUES**(expressiona), (expressionb), ..., (expressionk) - An IN predicate of the form:

is equivalent to a quantified predicate of the form:`(expressiona, expressionb,..., expressionk)`

**IN**(fullselect)

Note that the operand on the left side of this form of these predicates is referred to as a row-value-expression.`(expressiona, expressionb,..., expressionk) =`

**ANY**(fullselect)

The values for *expression1* and *expression2* or the column of *fullselect1* in the
IN predicate must be compatible. Each field of the *row-value-expression* 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, 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 condition 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 condition 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:

Expressions | Type | Code Page |
---|---|---|

COL_1 | column | 850 |

HV_2 | host variable | 437 |

HV_3 | host variable | 437 |

CON_1 | constant | 850 |

` 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 condition 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 condition 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)