|
|
A search condition is a single predicate or several predicates
connected by the logical operators AND or OR. A predicate is a comparison
of expressions that evaluates to a value of TRUE, FALSE, or unknown.
If a predicate evaluates to TRUE for a row, the row qualifies for
the select, update, or delete operation. If the predicate evaluates
to FALSE or unknown for a row, the row is not operated on.
SQL Data Manipulation Statements
[NOT] {BetweenPredicate
ComparisonPredicate
ExistsPredicate
InPredicate
LikePredicate
NullPredicate
QuantifiedPredicate
(SearchCondition) } [
{AND | OR} [NOT] {BetweenPredicate
ComparisonPredicate
ExistsPredicate
InPredicate
LikePredicate
NullPredicate
QuantifiedPredicate
(SearchCondition) } ] [...]
- NOT, AND, OR
are logical operators with the following functions:
- NOT
reverses the value of the predicate that follows it.
- AND
evaluates predicates it joins to TRUE if they are both
TRUE.
- OR
evaluates predicates it joins to TRUE if either or both are
TRUE.
- BetweenPredicate
determines whether an expression is within a certain range of
values.
- ComparisonPredicate
compares two expressions.
- ExistsPredicate
determines whether a subquery returns any non-null values.
- InPredicate
determines whether an expression matches an element within a specified
set.
- LikePredicate
determines whether an expression contains a particular character
string pattern.
- NullPredicate
determines whether a value is null.
- QuantifiedPredicate
determines whether an expression bears a particular relationship to a
specified set.
- (SearchCondition)
is one of the above predicates, enclosed in parentheses.
Predicates in a search condition are evaluated as follows:
Predicates in parentheses are evaluated first.
NOT is applied to each predicate.
AND is applied next, left to right.
OR is applied last, left to right.
When a predicate contains an expression that is null, the value of
the predicate is unknown. Logical operations on such a predicate
result in the following values, where a question mark (?) represents
the unknown value:
Figure 9-1 Logical Operations on Predicates Containing NULL
Values
When the search condition for a row evaluates to unknown, the row
does not satisfy the search condition and the row is not operated on.
Check constraints are an exception; see the section on CREATE TABLE
or CREATE VIEW.
You can compare only compatible data types. INTEGER, SMALLINT,
DECIMAL, FLOAT, and REAL are compatible. CHAR and VARCHAR are
compatible, regardless of length. You can compare items of type DATE,
TIME, DATETIME, and INTERVAL to literals of type CHAR or VARCHAR.
ALLBASE/SQL converts the literal before the comparison. BINARY and
VARBINARY are compatible, regardless of length.
You cannot include a LONG BINARY or LONG VARBINARY data type in a
predicate except within a long column function.
A SubQuery expression cannot appear on the
left-hand side of a predicate.
Refer to Chapter 7 "Data Types" and
Chapter 8 "Expressions" for information
concerning value extensions and type conversion during comparison
operations.
|