The BETWEEN predicate compares a value with a range of values. If the data types of the operands are not the same, all values are converted to the data type that would result by applying the "Rules for result data types", except if the data types of all the operands are numeric, in which case no values are converted.
value1 BETWEEN value2 AND value3
is
equivalent to the search condition: value1 >= value2 AND value1 <= value3
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 first operand (expression) cannot include a function that is not deterministic or has an external action (SQLSTATE 42845).
EMPLOYEE.SALARY BETWEEN 20000 AND 40000
Results in all salaries between $20,000.00 and $40,000.00.
SALARY NOT BETWEEN 20000 + :HV1 AND 40000
Assuming :HV1 is 5000, results in all salaries below $25,000.00 and above $40,000.00.