Syntax¶

<expr>[NOT]BETWEEN<lower_bound>AND<upper_bound>

Arguments¶

expr

The input expression.

lower_bound

The lower boundary.

upper_bound

The upper boundary.

Returns¶

The function returns a value of type BOOLEAN.

Usage notes¶

• exprBETWEENlower_boundANDupper_bound is equivalent toexpr>=lower_boundANDexpr<=upper_bound.

• The specified upper boundary must be greater than the lower boundary.

• The data types of the argument values must be the same orcompatible.

  If the function implicitly casts a value to a different data type, it might return unexpected results.

  For example, whenexpr is a TIMESTAMP value, and thelower_bound andupper_bound valuesare DATE values, the DATE values are implicitly cast to TIMESTAMP values, and the time is set to00:00:00. Forthe following WHERE clause, assumetimestamp_column is a column of type TIMESTAMP in a table:

  WHEREtimestamp_columnBETWEEN'2025-04-30'AND'2025-04-31'

  Copy

  When the DATE values are implicitly cast, the WHERE clause is interpreted as the following:

  WHEREtimestamp_columnBETWEEN'2025-04-30 00:00:00'AND'2025-04-31 00:00:00'

  Copy

  With this WHERE clause, the function returnsFALSE for virtually alltimestamp_column values on 2025-04-31,which might not be intended. To avoid this specific issue, you can specify the next day forupper_bound whenyou call the function:

  WHEREtimestamp_columnBETWEEN'2025-04-30'AND'2025-05-01'

  Copy

Collation details¶

The expressionABETWEENXANDY is equivalent toA>=XANDA<=Y. The collations used for comparingwithX andY are independent and do not need to be identical, but both need to be compatible with thecollation ofA.

Examples¶

Here are a few simple examples of using BETWEEN with numeric and string values:

SELECT'true'WHERE1BETWEEN0AND10;

+--------+| 'TRUE' ||--------|| true   |+--------+

SELECT'true'WHERE1.35BETWEEN1AND2;

+--------+| 'TRUE' ||--------|| true   |+--------+

SELECT'true'WHERE'the'BETWEEN'that'AND'then';

+--------+| 'TRUE' ||--------|| true   |+--------+

The following examples use COLLATE with BETWEEN:

SELECT'm'BETWEENCOLLATE('A','lower')ANDCOLLATE('Z','lower');

+-------------------------------------------------------------+| 'M' BETWEEN COLLATE('A', 'LOWER') AND COLLATE('Z', 'LOWER') ||-------------------------------------------------------------|| True                                                        |+-------------------------------------------------------------+

SELECTCOLLATE('m','upper')BETWEEN'A'AND'Z';

+-------------------------------------------+| COLLATE('M', 'UPPER') BETWEEN 'A' AND 'Z' ||-------------------------------------------|| True                                      |+-------------------------------------------+