Search conditions

A search condition specifies a condition that is true, false, or unknown about a given row or group.

Read syntax diagramSkip visual syntax diagramNOTpredicateSELECTIVITYnumeric-constant(search-condition)ANDORNOTpredicateSELECTIVITYnumeric-constant(search-condition)

Description

Start of change
SELECTIVITY numeric-constant

Specifies the expected selectivity percentage for the predicate. The selectivity value must be a numeric constant in the range from 0 to 1 (inclusive). For example, if you specify 0.01, the predicate 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 trigger event predicate, or if it is used with a search-condition that is part of a check constraint or in a CREATE INDEX statement.

Some predicates are rewritten during query processing. Since a SELECTIVITY clause cannot be properly applied to the rewritten predicates, the following predicates will usually ignore a user-supplied selectivity value:
  • DISTINCT and NOT DISTINCT
  • NOT BETWEEN
  • EXISTS
  • IN with a list of values
  • row-value-expression compared to row-value-expression
End of change

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 Tables 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, NOT(false) is true, and NOT(unknown) is 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.

Examples

In the examples, the numbers on the second line indicate the order in which the operators are evaluated.

Example 1

  MAJPROJ = 'MA2100' AND DEPTNO = 'D11'  OR  DEPTNO = 'B03' OR DEPTNO = 'E11'
                      ↑                   ↑                  ↑    
                     ┌┴┐               ┌──┴───┐           ┌──┴───┐
                     │1│               │2 or 3│           │2 or 3│
                     └─┘               └──────┘           └──────┘

Example 2

MAJPROJ = 'MA2100'  AND  (DEPTNO = 'D11'  OR  DEPTNO = 'B03')  OR  DEPTNO = 'E11'
                     ↑                     ↑                    ↑ 
                    ┌┴┐                   ┌┴┐                  ┌┴┐
                    │2│                   │1│                  │3│
                    └─┘                   └─┘                  └─┘