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
|
