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

<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>.

 

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.

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

 

Examples

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

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

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

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