>>-expression----+------+--------------------------------------->
'-NOT--'
>----BETWEEN--expression--AND--expression----------------------><
The BETWEEN predicate compares a value with a range of values.
The BETWEEN predicate:
value1 BETWEEN value2 AND value3
is equivalent to the search condition:
value1 >= value2 AND value1 <= value3
The BETWEEN predicate:
value1 NOT BETWEEN value2 AND value3
is equivalent to the search condition:
NOT(value1 BETWEEN value2 AND value3); that is, value1 < value2 OR value1 > value3.
The values for the expressions in the BETWEEN predicate can have different code pages. The operands are converted as if the above equivalent search conditions were specified.
The first operand (expression) cannot include a function that is variant or has an external action (SQLSTATE 426804).
Given a mixture of datetime values and string representations of datetime values, all values are converted to the data type of the datetime operand.
Examples:
Example 1
EMPLOYEE.SALARY BETWEEN 20000 AND 40000
Results in all salaries between $20,000.00 and $40,000.00.
Example 2
SALARY NOT BETWEEN 20000 + :HV1 AND 40000
Assuming :HV1 is 5000, results in all salaries below $25,000.00 and above $40,000.00.
Example 3
Given the following:
| Expressions | Type | Code Page |
|---|---|---|
| HV_1 | host variable | 437 |
| HV_2 | host variable | 437 |
| Col_1 | column | 850 |
When evaluating the predicate:
:HV_1 BETWEEN :HV_2 AND COL_1
It will be interpreted as:
:HV_1 >= :HV_2 AND :HV_1 <= COL_1
The first occurrence of :HV_1 will remain in the application code page since it is being compared to :HV_2 which will also remain in the application code page. The second occurrence of :HV_1 will be converted to the database code page since it is being compared to a column value.