Background documentationEquality Condition Locate this document in the navigation structure

 

An equality condition is a search condition with a comparison predicate.

Prerequisites

The SQL optimizer can only evaluate an equality condition if it meets the following conditions:

  • Only the relational operator (=) was used as operator.

  • It has one of the following forms:

    Syntax Syntax

    1. <column_spec> = <extended_value_spec>
<column_spec> = <subquery>
    End of the code.

    If an equality condition has a different form, the SQL optimizer attempts to transform it. The SQL optimizer transforms the following expressions into conditions without NOT with the negated operator =:

    Syntax Syntax

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

    Note Note

    The SQL optimizer can only transform 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.

Equality Condition

EXPLAIN SELECT *

  FROM hotel.customer

    WHERE name = 'Smith'

  • Qualification: name = 'Smith'

  • 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, the database system can use the strategy FULL_NAME_INDEX (name,firstname).

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

6

More Information

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