This determines whether a row occurs in a table.
{ row_1 [NOT] IN { subquery_2 | ( row_2 , ...) } |
vector_column [NOT] IN ( expression , ...) }
row_1 ::= { ( expression ,...) | expression | subquery_1 }
row_2 ::= { ( expression ,...) | expression }
vector_column ::= [ table .] { column[min..max] | column ( min..max ) }
row_1
returns one row.
Each expression in row_1 must be atomic. The row consists of the expression values in the order specified. A single expression therefore returns a row with one column.
subquery_1
must return a table without multiple columns and with at most one row. This row is the left-hand operand. If the table returned is empty, the operand is a row with the NULL value in each column.
subquery_2
this table is the right-hand operand.
row_2
The right-hand operand is the table whose individual row(s) are specified with row_2. If row_2 is specified several times then the data type of each column of the table is determined by the rules described under "Data type of the derived column for UNION" .
row_1, row_2, subquery_1 and subquery_2 must all have the same number of columns; the data types of the corresponding columns must be compatible (see section "Compatibility between data types").
vector_column
A multiple column with special rules for the result. The column specification may not be an external reference.
expression
The values must be atomic and their data types must be compatible with the data type of the vector_column occurrences ( section "Compatibility between data types").
Result
row_1 IN subquery_2 or row_1 IN (row_2 ,...):
True if the comparison for equality of row_1 with at least one row of the right-hand operand yields true.
False if all the comparisons for equality of row_1 with some row of the right-hand operand yield false, or if the right-hand operand is a subquery which returns an empty table.
Unknown in all other cases.
row_1 NOT IN subquery_2 or row_1 NOT IN (row_2 ,...):
is identical to:
NOT (row_1 IN subquery_2) or. NOT (row_1 IN ( row_2 ,...))
The comparison rules for “=” apply (see also section "Comparison rules").
vector_column [NOT] IN (expression, ,...)
The IN predicate is evaluated for each occurrence of vector_column.
The individual results are combined with OR.
Example
If X is a multiple column with 3 elements, the range query X[1..3] BETWEEN 13 AND 30 is equivalent to the following element queries:
X[1] IN (13, 20, 30) OR X[2] IN (13, 20, 30) OR X[3] IN (13, 20, 30)
Examples
IN predicate with single rows as right-hand operand:
Select the customers from Munich or Berlin from the CUSTOMERS table.
SELECT company, cust_info, city FROM customer
WHERE city IN ('Munich','Berlin')
company cust_info city
Siemens AG Electrical Munich
Login GmbH PC networks Munich
Plenzer Trading Fruit market Munich
Freddys Fishery Unit retail Berlin
IN predicate with subquery as right-hand operand:
Select the orders for which no training was performed from the ORDERS and SERVICE tables.
SELECT cust_num FROM orders
WHERE order_num NOT IN (SELECT order_num FROM service WHERE service_text = 'Training')