602SQL Documentation Index  
<­Using Indexes in Prefix-like Queries UpDatabase Table Indexes CREATE INDEX Statement­>

Using Indexes not Containing NULL Values

Indexes not containing NULL values have only a limited use when optimizing queries. They're used for search for records with the specified restrictions:

Indexes not containing NULL values are not used for sorting records based on the index key value (ORDER BY) and for searching for records with the specified restrictions:

because the record whose index key is NULL won't be found using this index.

It's better to use indexes that may contain NULL values for ensuring effective query evaluation.

If it's desired to search for records for the same index key, watch the key value uniqueness and allow NULL value duplicity, you have to create two indices: one index containing NULL values which is not unique (for search) and one unique index not containing NULL values (for uniqueness check): 

INDEX (column) NULL,
UNIQUE (column) NOT NULL

Incorrect Query Evaluation in the Compatibility Mode with the Older Versions

If the SQLOPT_NULLEQNULL compatibility attribute is set, then the result of comparing two NULL values is TRUE. This may lead to incorrect evaluation of a certain class of queries over tables, that have indexes not containing the NULL value.

The affected queries contain a condition: 

column=value
where the column has an index not containing NULL values in the table and the value is a dynamic parameter, client variable, variable in a SQL routine, another column or a expression. If the variable is assigned NULL value, then if the query optimizer uses the index for the column not containing NULL values, the query result won't contain the record, whose column equals NULL.

A solution to this problem:

<­Using Indexes in Prefix-like Queries UpDatabase Table Indexes CREATE INDEX Statement­>