Equality Condition 

Equality conditions are search conditions with a comparison predicate.

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

●     The only operator is the relational operator =

●     The equality conditions have one of the following forms:

<column_spec> = <extended_value_spec>

<column_spec> = <subquery>

The equality condition must have one of these forms before the SQL Optimizer can evaluate it to determine a search strategy. If the equality condition has another form, the SQL Optimizer attempts to transform it.

The SQL Optimizer transforms expressions in the following forms into conditions without NOT, with the negated operator =:

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

...NOT (<column_spec> <> <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 = 'Smith'

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

The index FULL_NAME_INDEX (name, firstname) can be used to find all the entries with the name Smith.

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

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

See also:

EXPLAIN Statement

SQL Reference Manual, Comparison Predicate (comparison_predicate)