IN Condition 
An IN condition is a search condition with an IN predicate.
Prerequisites
The SQL optimizer can only evaluate an IN condition if it meets the following conditions:
Only the IN operator is used as operator.
It has one of the following forms:
The database system converts conditions of the following form, if possible, into an expression without NOT with a correspondingly negated operator:
Syntax
... NOT (<column_spec> NOT IN (<extended_value_spec>, ...)) ... NOT (<column_spec> NOT IN <subquery>)
Note
The SQL optimizer can only transfor queries if the QueryRewrite function is enabled (special database parameter EnableQueryRewrite).
More information: Database Administration, Special Database Parameters
Example
The following example uses the demo database DEMODB with the complete demo data in the schema HOTEL, see Concepts of the Database System, Objects in the Schema HOTEL.
IN Condition
EXPLAIN SELECT *
FROM hotel.customer
WHERE name IN ('Smith','Miller')
Qualification: name IN ('Smith', 'Miller')
Primary key of CUSTOMER table: cno
Indexes via qualified columns: FULL_NAME_INDEX (name,firstname)
Search strategy: RANGE CONDITION FOR INDEX
TABLENAME |
COLUMN_OR_INDEX |
STRATEGY |
PAGECOUNT |
|---|---|---|---|
CUSTOMER |
FULL_NAME_INDEX |
RANGE CONDITION FOR INDEX |
34 |
NAME |
(USED INDEX COLUMN) |
||
RESULT IS NOT COPIED, COSTVALUE IS |
21 |
To find all data records with the name Smith and Miller, the SQL optimizer can use the strategy FULL_NAME_INDEX (name,firstname).
Note that depending on the data, the SQL optimizer might also choose the IN CONDITION FOR strategy.