BETWEEN predicate

The BETWEEN predicate determines whether a given value lies between two other given values that are specified in ascending order.

Each of the predicate's two forms has an equivalent search condition, as shown in the following table:

Table 1. BETWEEN predicate and equivalent search conditions
BETWEEN predicate	Equivalent search condition
`value1 BETWEEN value2 AND value3`	`value1 >= value2 AND value1 <= value3`¹
`value1 NOT BETWEEN value2 AND value3` or, equivalently: `NOT(value1 BETWEEN value2 AND value3)`	`value1 < value2 OR value1 > value3`¹
Note: 1. Might not be equivalent if `value1`, `value2`, or `value3` are columns or derived values based on columns that are not the same CCSID set because the clause is evaluated in Unicode.

Search conditions are discussed in Search conditions.

If the operands include a mixture of datetime values and valid string representations of datetime values, all values are converted to the data type of the datetime operand.

Example: Consider the following predicate:

  A BETWEEN B AND C

The following table shows the value of the predicate for various values of A, B, and C.

Value of `A`	Value of `B`	Value of `C`	Value of predicate
1,2, or 3	1	3	true
0 or 4	1	3	false
0	1	null	false
4	null	3	false
null	any value	any value	unknown
2	1	null	unknown
3	null	4	unknown