IN Condition

IN conditions are search conditions with an IN predicate.

For the SQL Optimizer, the following restrictions apply to IN conditions:

?     The operator is the operator IN.

?     IN conditions have one of the following forms:

<column_spec> IN (<extended_value_spec>,...)
<column_spec>
IN <subquery>

The IN condition must have one of these forms before the SQL Optimizer can evaluate it to determine a search strategy.

Conditions with the following format are converted to an expression without NOT and with the appropriate negated operator, if possible:

...NOT (<column_spec> NOT IN (<extended_value_spec>,...))

...NOT (<column_spec> NOT IN <subquery>)

The SQL Optimizer then continues to process the transformed condition.

Examples

The examples used to explain the SQL Optimizer are based on the demo database DEMODB and its complete demo data in the schema HOTEL.

Concepts of the Database System, Objects in the Schema HOTEL

SELECT * FROM hotel.customer
  WHERE name IN ('Smith', 'Miller')

Qualification: name IN ('Smith', 'Miller')M
Primary key of the table CUSTOMER:
cno
Indexes of qualified columns
: FULL_NAME_INDEX (name, firstname)
Search strategy used: RANGE CONDITION FOR INDEX

The indexFULL_NAME_INDEX (name, firstname) can be used to find all the entries with the name Smith or Miller.

Result of the EXPLAIN Statement

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 will find additional simple example SQL statements in the SQL Tutorial.

See also:

EXPLAIN Statement

SQL Reference Manual, IN Predicate (in_predicate)