Show TOC

In predicateLocate this document in the navigation structure

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.

    Caution

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