3. Elements of SQL Statements : Search Conditions in SQL Statements
 
Share this page                  
Search Conditions in SQL Statements
Search conditions are used in WHERE and HAVING clauses to qualify the selection of data. Search conditions are composed of predicates of various kinds, optionally combined using parentheses and logical operators (AND, OR, and NOT). The following are examples of legal 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 (that is, AND, OR, NOT) 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, not unknown. Unknown values are considered false.
More information:
Comparison Operators
Logical Operators
Predicates in SQL