The WHERE clause of the SELECT statement controls the amount of information that the query evaluates. This clause can consist of a comparison condition, which evaluates to a BOOLEAN value. Therefore, a comparison condition can contain a Boolean function; that is, it can contain a user-defined function that returns a BOOLEAN value. Boolean functions can act as filters in queries, as Table 116 shows.
| Comparison Condition | Operator Symbol | Associated User-Defined Function |
|---|---|---|
| Relational operator | =, !=, <>
<, <= >, >= |
equal( ), notequal( ), notequal( )
lessthan( ), lessthanorequal( ) greaterthan( ), greaterthanorequal( ) |
| LIKE, MATCHES | None | like( ), matches( ) |
| Boolean function | None | Name of a user-defined function that returns a BOOLEAN value |
The Boolean functions in Table 116 can act as filters in queries. To optimize queries that use these functions as filters, you can define the following UDR-optimization functions.
| Type of Optimization | Description |
|---|---|
| Negator function | Calculate the NOT condition of the Boolean expression |
| Selectivity and cost functions | Provide an estimate of the number of rows that the filter will return |