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)