4. Elements of OpenSQL Statements : Search Conditions
 
Share this page                  
Search Conditions
Search conditions are used in where and having clauses to qualify the selection of data. Search conditions are composed of one or more predicates. Multiple predicates can be combined using parentheses and the logical operators (and, or, and not). The following examples illustrate possible combinations of search conditions:
Description
Example
Simple predicate
salary between 10000 and 20000
Predicate with not operator
edept not like 'eng_%'
Predicates combined using
or operator
edept like 'eng_%' or edept like 'admin_%'
Predicates combined using
and operator
salary between 10000 and 20000 and edept like 'eng_%'
Predicates combined using parentheses to specify evaluation
(salary between 10000 and 20000 and edept like 'eng_%' ) or edept like 'admin_%'
Predicates evaluate to true, false, or unknown. They evaluate to unknown if one or both operands are null (the is null predicate is the exception). When predicates are combined using logical operators (not, and, and or) to form a search condition, the search condition evaluates to true, false, or unknown as shown in the following tables:
and
true
false
unknown
true
true
false
unknown
false
false
false
false
unknown
unknown
false
unknown
 
or
true
false
unknown
true
true
true
true
false
true
false
unknown
unknown
true
unknown
unknown
Not(true) is false, not(false) is true, not(unknown) is unknown.
After all search conditions are evaluated, the value of the where or having clause is determined. The where or having clause can be true or false only. Unknown values are considered false. For more information about predicates and logical operators, see Predicates (see page Predicates) and Logical Operators (see page Logical Operators) in this chapter.