 |
» |
|
|
|
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
![[Logical Operations on Predicates Containing NULL Values]](img/logoper.gif)
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 the "Data Types" and "Expressions" chapters for information
concerning value extensions and type conversion during comparison operations.
|
