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>.
<in predicate> ::= <value expression> ( NOT )? IN '(' <in predicate value> ')'. |
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.
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 |
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.