Background documentationIN Condition Locate this document in the navigation structure

 

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:

Syntax Syntax

  1. ... NOT (<column_spec> NOT IN (<extended_value_spec>, ...))
    ... NOT (<column_spec> NOT IN <subquery>)
End of the code.

Note 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

End of the note.

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

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

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

You can find additional simple example SQL statements in the SQL Tutorial.