BETWEEN predicate

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

The BETWEEN predicate is supported only for Java™ application programs.

Read syntax diagramSkip visual syntax diagram columnalias NOTBETWEENliteral ANDliteral
column
Read syntax diagramSkip visual syntax diagramschema-name.table-name.column-name1
Notes:
  • 1 You can have the same column name in multiple tables, but if the table is not qualified, each table must be searched for the column.

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
column1 BETWEEN value1 AND value2 column1 >= value1 AND columnn1 <= value2
column1 NOT BETWEEN value1 AND value2 column1 < value1 OR column1 > value2

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
null any value any value false