ALLBASE/SQL Reference Manual (36216-90216)

264 Chapter9
Search Conditions
BETWEEN Predicate
BETWEEN Predicate
A BETWEEN predicate determines whether a value is equal to or greater than a second
value
and equal to or less than a third value. The predicate evaluates to true if a value falls
within the specified range. If the NOT option is used, the predicate evaluates to true if a
value does
not fall within the specified range.
Note that the second value must be less than or equal to the third value for BETWEEN to
possibly be TRUE and for NOT BETWEEN to possibly be FALSE.
Scope
SQL Data Manipulation Statements
SQL Syntax
Expression1 [NOT]BETWEEN Expression2 AND Expression3
Parameters
Expression1, 2, 3 specify values used to identify columns, screen rows, or
define new column values. The syntax for expressions is
defined in the "Expressions" chapter. Both numeric and
non-numeric expressions are allowed in BETWEEN
predicates.
NOT is a logical operator and reverses the value of the predicate
that follows it.
Description
• Expression2 and Expression3 constitute a range of possible values for which
Expression2 is the lowest possible value and Expression3 is the highest possible
value. In the BETWEEN predicate, the low value must come before the high value. Also in
the BETWEEN predicate, subqueries are not allowed.
• Comparisons are conducted as described under "Comparison Predicates" later in this
chapter.
Example
Parts sold for under $250.00 and over $1500.00 are discounted by 25 percent.
UPDATE PurchDB.Parts SET SalesPrice = SalesPrice * .75
WHERE SalesPrice NOT BETWEEN 250.00 AND 1500.00