Search conditions

A search condition specifies a condition that is true, false, or unknown about a given row or group. When the condition is true, the row or group qualifies for the results. When the condition is false or unknown, the row or group does not qualify.


>>-+-----+--+-predicate--+-------------------------------+-+---->
   '-NOT-'  |            '-SELECTIVITY--numeric-constant-' |   
            '-(search-condition)---------------------------'   

   .----------------------------------------------.   
   V                                              |   
>----+------------------------------------------+-+------------><
     '-+-AND-+--+-----+--+-predicate----------+-'     
       '-OR--'  '-NOT-'  '-(search-condition)-'

Description

SELECTIVITY numeric-constant: Specifies the expected selectivity percentage for the predicate. You can specify the SELECTIVITY clause only when the predicate contains one of the indexable spatial predicate functions and the predicate is in the form of spatial-predicate-function operator expression, where operator is either = or <. The selectivity value must be an integer or decimal constant value in the range from 0 to 1 (inclusive). For example, if you specify 0.01, the spatial predicate function is expected to filter out all but one percent of all the rows in the table. An error is returned if the SELECTIVITY clause is specified for a non-spatial predicate function.

The result of a search condition is derived by application of the specified logical operators (AND, OR, NOT) to the result of each specified predicate. If logical operators are not specified, the result of the search condition is the result of the specified predicate.

AND and OR are defined in the following table, in which P and Q are any predicates:

Table 1. Truth table for AND and OR
`P`	`Q`	`P` and `Q`	`P` or `Q`
True	True	True	True
True	False	False	True
True	Unknown	Unknown	True
False	True	False	True
False	False	False	False
False	Unknown	False	Unknown
Unknown	True	Unknown	True
Unknown	False	False	Unknown
Unknown	Unknown	Unknown	Unknown

NOT(true) is false and NOT(false) is true, but NOT(unknown) is still unknown. The NOT logical operator has no affect on an unknown condition. The result of NOT(unknown) is still unknown.

Search conditions within parentheses are evaluated first. If the order of evaluation is not specified by parentheses, NOT is applied before AND, and AND is applied before OR. The order in which operators at the same precedence level are evaluated is undefined to allow for optimization of search conditions.

Example 1: In the first of the search conditions below, AND is applied before OR. In the second, OR is applied before AND.

  SALARY>:SS AND  COMM>:CC OR BONUS>:BB
  SALARY>:SS AND (COMM>:CC OR BONUS>:BB)

Example 2: In the first of the search conditions below, NOT is applied before AND. In the second, AND is applied before NOT.

  NOT SALARY>:SS  AND  COMM>:CC
  NOT (SALARY>:SS AND  COMM>:CC)

Example 3: For the following search condition, AND is applied first. After the application of AND, the OR operators could be applied in either order without changing the result. DB2® can therefore select the order of applying the OR operators.

  SALARY>:SS AND COMM>:CC OR BONUS>:BB OR SEX=:GG