BETWEEN predicate

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.

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 first operand (expression) cannot include a function that is not deterministic or has an external action (SQLSTATE 42845).

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.