# BETWEEN

## Overview

Inclusion in a range.

## USAGE

```check BETWEEN low AND high
```

Value Name

Description

`check`

Value expressions

`low`

Value expressions

`high`

Value expressions

## DETAILS

The `BETWEEN` operator checks if the `check` value is within the range of `low` to `high` inclusive. This is equivalent to `(check >= low AND check <= high)`

The values to be compared can be integers, decimals, or strings. Strings are compared using string comparisons, not numeric conversions.

The return value is a `1` (true) when the number is in range, a `0` (false) when it is not in range, or `NULL` when the `check` value is `NULL`.

The return value is sometimes `NULL` when the `low` and/or `high` value is `NULL`, but it can also be `1`.

The return value is always `1` if the range is specified with `low > high` instead of `low <= high`.

## EXAMPLES

```SELECT 5 BETWEEN 2 AND 5, 5 BETWEEN 5 AND 2;
```
```+-------------------+-------------------+
| 5 BETWEEN 2 AND 5 | 5 BETWEEN 5 AND 2 |
+-------------------+-------------------+
|                 1 |                 0 |
+-------------------+-------------------+
```
```CREATE TABLE between_example (
v1 INT,
v2 INT
);
```
```INSERT INTO between_example VALUES
(0, 3), (1, 4), (2, 5), (3, 6), (4, 7),
(5, 8), (6, 9), (7, 1), (8, 0);
```
```SELECT v1
FROM between_example
WHERE v1 BETWEEN 5 AND 7;
```
```+------+
| v1   |
+------+
|    5 |
|    6 |
|    7 |
+------+
```
```SELECT CONCAT(v1, ' <= 4 <= ', v2) AS checking,
4 BETWEEN v1 AND v2 AS result
FROM between_example;
```
```+-------------+--------+
| checking    | result |
+-------------+--------+
| 0 <= 4 <= 3 |      0 |
| 1 <= 4 <= 4 |      1 |
| 2 <= 4 <= 5 |      1 |
| 3 <= 4 <= 6 |      1 |
| 4 <= 4 <= 7 |      1 |
| 5 <= 4 <= 8 |      0 |
| 6 <= 4 <= 9 |      0 |
| 7 <= 4 <= 1 |      0 |
| 8 <= 4 <= 0 |      0 |
+-------------+--------+
```

## CHANGE HISTORY

Release Series

History

23.09

• Present starting in MariaDB Xpand 23.09.1.

6.1

• Present starting in MariaDB Xpand 6.1.0.

6.0

• Present starting in MariaDB Xpand 6.0.3.

5.3

• Present starting in MariaDB Xpand 5.3.13.

Release Series

History

6.0

• Present starting in MariaDB Xpand 6.0.3.

5.3

• Present starting in MariaDB Xpand 5.3.13.

Release Series

History

6.1

• Present starting in MariaDB Xpand 6.1.0.