SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—540440-003
6-82
BETWEEN Predicate
The BETWEEN predicate determines whether a sequence of values is within a range
of sequences of values.
row-value-constructor
specifies an operand of the BETWEEN predicate. The three operands can be
either of:
(expression [,expression ]...)
is a sequence of SQL value expressions, separated by commas and enclosed
in parentheses. expression cannot include an aggregate function unless
expression is in a HAVING clause. expression can be a scalar subquery
(a subquery that returns a single row consisting of a single column). See
Expressions on page 6-37.
row-subquery
is a subquery that returns a single row (consisting of a sequence of values).
See Subquery on page 6-105.
The three row-value-constructors specified in a BETWEEN predicate must
contain the same number of elements. That is, the number of value expressions in
each list, or the number of values returned by a row subquery, must be the same.
The data types of the respective values of the three row-value-constructors
must be comparable. Respective values are values with the same ordinal position
in the two lists. See Comparable and Compatible Data Types
on page 6-16.
Considerations for BETWEEN
Logical Equivalents Using AND and OR
The predicate expr1 BETWEEN expr2 AND expr3 is true if and only if this condition
is true:
expr2 <= expr1 AND expr1 <= expr3
The predicate expr1 NOT BETWEEN expr2 AND expr3 is true if and only if this
condition is true:
expr2 > expr1 OR expr1 > expr3
Descending Columns in Keys
row-value-constructor [NOT] BETWEEN
row-value-constructor AND row-value-constructor
row-value-constructor is:
(expression [,expression ]...)
| row-subquery