 |
» |
|
|
|
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. Scope | |
SQL Data Manipulation Statements SQL Syntax | |
[NOT] {BetweenPredicate
ComparisonPredicate
ExistsPredicate
InPredicate
LikePredicate
NullPredicate
QuantifiedPredicate
(SearchCondition) } [ {AND
OR} [NOT] {BetweenPredicate
ComparisonPredicate
ExistsPredicate
InPredicate
LikePredicate
NullPredicate
QuantifiedPredicate
(SearchCondition) } ] [...] |
Parameters | |
- 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.
Description | |
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.
|
