Show TOC

IN ConditionLocate this document in the navigation structure

Use

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:

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>)
Note

The SQL optimizer can only transfer queries if the QueryRewrite function is enabled (special database parameter EnableQueryRewrite).

For more information, see 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.

More Information