Predicate types

The type of a predicate depends on its operator or syntax. The type determines what type of processing and filtering occurs when DB2® evaluates the predicate.

Predicates can be organized into the following types:

Subquery predicates

Any predicate that includes another SELECT statement. For example:

C1 IN (SELECT C10 FROM TABLE1)

Equal predicates

Any predicate that is not a subquery predicate and has an equal operator and no NOT operator. Also included are predicates of the form C1 IS NULL and C IS NOT DISTINCT FROM. For example:

C1=100

Assume that a unique index, I1 (C1), exists on table T1 (C1, C2), and that all values of C1 are positive integers. DB2 chooses index access for the following query that contains an equal predicate because the index is highly selective on column C1:

SELECT * FROM T1 WHERE C1 = 0;

Range predicates

Any predicate that is not a subquery predicate and contains one of the following operators:

• >
• >=
• <
• <=
• LIKE
• BETWEEN

For example:

C1>100

Assume that a unique index, I1 (C1), exists on table T1 (C1, C2), and that all values of C1 are positive integers. The range predicate in the following query does not eliminate any rows of T1. Therefore, DB2 might determine during access path selection that a table space scan is more efficient than the index scan.

SELECT C1, C2 FROM T1 WHERE C1 >= 0;

IN predicates

A predicate of the form column IN (list of values). For example:

C1 IN (5,10,15)

NOT predicates

Any predicate that is not a subquery predicate and contains a NOT operator. Also included are predicates of the form C1 IS DISTINCT FROM. For example:

C1 <> 5 or C1 NOT BETWEEN 10 AND 20