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 |
