Categories:

Conditional expression functions

# [ NOT ] BETWEENÂ¶

Returns `TRUE` when the input expression (numeric or string) is within the specified lower and upper boundary.

## SyntaxÂ¶

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

## Usage notesÂ¶

• `expr BETWEEN lower_bound AND upper_bound` is equivalent to `expr >= lower_bound AND expr <= upper_bound`.

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

## Collation detailsÂ¶

The expression `A BETWEEN X AND Y` is equivalent to `A >= X AND A <= Y`. The collations used for comparing with `X` and `Y` are independent and do not need to be identical, but both need to be compatible with the collation of `A`.

## ExamplesÂ¶

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

```SELECT 'true' WHERE 1 BETWEEN 0 AND 10;
```
```+--------+
| 'TRUE' |
|--------|
| true   |
+--------+
```
```SELECT 'true' WHERE 1.35 BETWEEN 1 AND 2;
```
```+--------+
| 'TRUE' |
|--------|
| true   |
+--------+
```
```SELECT 'true' WHERE 'the' BETWEEN 'that' AND 'then';
```
```+--------+
| 'TRUE' |
|--------|
| true   |
+--------+
```

The following examples use COLLATE with BETWEEN:

```SELECT 'm' BETWEEN COLLATE('A', 'lower') AND COLLATE('Z', 'lower');
```
```+-------------------------------------------------------------+
| 'M' BETWEEN COLLATE('A', 'LOWER') AND COLLATE('Z', 'LOWER') |
|-------------------------------------------------------------|
| True                                                        |
+-------------------------------------------------------------+
```
```SELECT COLLATE('m', 'upper') BETWEEN 'A' AND 'Z';
```
```+-------------------------------------------+
| COLLATE('M', 'UPPER') BETWEEN 'A' AND 'Z' |
|-------------------------------------------|
| True                                      |
+-------------------------------------------+
```
Language: English