SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
B-4
BETWEEN Predicate
BETWEEN Predicate
BETWEEN is a predicate that determines whether a value is within a range of values.
Considerations—BETWEEN

BETWEEN is a comparison predicate. For more information on general rules for
comparisons and specific information about comparing character data (including
character data associated with collations), numeric data, date-time data, and
interval data, see Comparison Predicate on page C-58.

If you specify expr1 BETWEEN expr2 AND expr3, the predicate is true if this
condition is true:
expr1 >= expr2 AND expr1 <= expr3

The three row-value-specs must contain the same number of expressions.

The data type of the result of an expression in one row-value-spec must be
compatible with the data types of the results of the two expressions in the same
ordinal position in the others.

If you specify NOT, the predicate is true if this condition is true:
( expr2 < expr1 OR expr1 > expr3 )
Specify NOT:
expr1 NOT BETWEEN expr2 AND expr3

For a clause that specifies a column in a key BETWEEN expr1 and expr2,
expr2 must be greater than expr1 even if the column is defined as
DESCENDING.
Examples—BETWEEN

This example finds those items for which the total price of the units in inventory is
in the range $1,000 to $10,000:
QTY_ON_HAND * PRICE
BETWEEN 1000.00 AND 10000.00
row-value-spec [NOT] BETWEEN row-value-spec AND
row-value-spec
row-value-spec is:
{expression[ ,expression] ... }
{ (expression[ ,expression] ... ) }