IN Condition 
A IN condition is a search condition with a IN predicate. The SQL Optimizer can only evaluate an IN condition if it meets the following conditions:
The IN operator is used as the operator.
The IN condition has one of the following forms:
<column_spec> IN (<extended_value_spec>,...)
<column_spec> IN <subquery>
The database system converts conditions of the following form, if possible, into an expression without NOT with a correspondingly negated operator:
... NOT (<column_spec> NOT
IN (<extended_value_spec>,...))
... NOT (<column_spec> NOT
IN <subquery>)
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
Example
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
To find all data records with the name Smith and Miller, the database system can use the strategy FULL_NAME_INDEX (name,firstname).
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 |
You can find additional simple example SQL statements in the SQL Tutorial.
More Information
SQL Reference Manual, IN Predicate (in_predicate)