In predicate

The <in predicate> is used to test if a value is contained in a set of values. The set of values can be specified either as a list or as the result of a query. In the later case the in predicate is equivalent to a <quantified comparison predicate>.

Syntax

Syntax Syntax

<in predicate> ::= <value expression> ( NOT )? IN '(' <in predicate value> ')'.

<in predicate value> ::= <query specification> 
                                    | <in value list>.

<in value list> ::= <in value> ( ',' <in value> )*.
<in value> ::= <dynamic parameter specification> | <literal>.

End of the code.

The <in predicate> "value NOT IN ( something )" is equivalent to the <boolean factor> "NOT ( value IN ( something ) )".

If the <in predicate value> is a <query specification> Q, then

Q must have the a result of one column. The data type of the <value expression> must be comparable to the type of that column. The result of the <in predicate> is

true, if the result of the comparison of the <value expression> to at least one row of the result of the query Q is true.
false, if the result of the query Q is empty or the result of the comparison of the <value expression> to all rows of the result of the query Q is false.
unknown, else.

(NB: This is equivalent to the <quantified comparison predicate> "val = ANY ( Q )".)

If the <in predicate value> is a <in value list> IVL, then

All <in value>s in the IVL must be comparable to the data type of the <value expression>. The result of the <in predicate> is

true, if the result of the comparison of the <value expression> to at least one <in value> of the IVL is true.
false, if the result of the comparison of the <value expression> to all <in value>s of the IVL is false.
unknown, else.

Caution

The number of <in value>s that an <in value list> may contain is database-dependent. More information: Open SQL Database Dependencies.

End of the caution.

Examples

Syntax Syntax

```
SELECT * FROM a WHERE c IN ( 1, 2 )
```

End of the code.

The IN List Predicate. All rows from table a where the column a.c has either the value 1 or the value 2 are selected.

Syntax Syntax

SELECT employee_name 
            FROM employees 
            WHERE employee_id 
                       IN ( SELECT employee_id FROM well_payed_epmloyees )

End of the code.

The IN Subquery Predicate. Selects the names of all employees from the table employees that have an employee_id equal to any employee_id contained in the table well_payed_employees.

More Information

Value expressions

Dynamic Parameter Specification

Literals