# Predicate transformations

WHERE clauses with predicates joined by OR are usually not optimizable. WHERE clauses with predicates joined by AND are optimizable if at least one of the predicates is optimizable.

For example:

    SELECT * FROM Flights
    WHERE flight_id = 'AA1111'
    AND segment_number <> 2

In this example, the first predicate is optimizable; the second predicate is not. Therefore, the statement is optimizable.

Note: In a few cases, a WHERE clause with predicates joined by OR can be transformed into an optimizable statement. See OR transformations.

Derby can transform some predicates internally so that at least one of the predicates is optimizable and thus the statement is optimizable. This section describes the predicate transformations that Derby performs to make predicates optimizable.

A predicate that uses the following comparison operators can sometimes be transformed internally into optimizable predicates.

- BETWEEN transformations
  A BETWEEN predicate is transformed into equivalent predicates that use the >= and <= operators, which are optimizable.
- LIKE transformations
  This section describes using LIKE transformations as a comparison operator.
- Simple IN predicate transformations
  A simple IN list predicate is a predicate where the left operand is a simple column reference and the IN list is composed entirely of constants or parameter markers.
- NOT IN predicate transformations
  NOT IN lists are transformed into multiple predicates that use the <> operator.
- OR transformations
  If all the OR predicates in a WHERE clause have a particular form, they might be optimizable.

Parent topic: Internal language transformations
Related concepts
Transitive closure
View transformations
Subquery processing and transformations
Outer join transformations
Sort avoidance
Aggregate processing