Useful indexes can use qualifiers

Matching index scans can use qualifiers that further restrict the result set. Remember that a WHERE clause that contains at least one optimizable predicate is optimizable. Nonoptimizable predicates can be useful in other ways.

Consider the following query:

SELECT *
FROM FLIGHTS
WHERE orig_airport < 'BBB'
AND orig_airport <> 'AKL'

The second predicate is not optimizable, but the first predicate is. The second predicate becomes a qualification for which Derby evaluates the entries in the index as it traverses it.

The following comparisons are valid qualifiers:
- =
- <
- <=
- >
- >=
- IS NULL
- BETWEEN
- LIKE
- <>
- IS NOT NULL
The qualifier's reference to the column does not have to be a simple column reference; you can put the column in an expression.
The qualifier's column does not have to be the first column in the index and does not have to be contiguous with the first column in the index.