ALLBASE/SQL Reference Manual > Chapter 9 Search Conditions

BETWEEN Predicate

	MPE documents
	Complete PDF
	Table of Contents
	Index

	E0300 Edition 9 ♥
	E0399 Edition 8
	E0897 Edition 7

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

Search Condition

Comparison Predicate