NOT BETWEEN

Overview

Negated inclusion in a range.

USAGE

```check NOT BETWEEN low AND high
```

Value Name

Description

`check`

Value expressions

`low`

Value expressions

`high`

Value expressions

DETAILS

The `NOT BETWEEN` operator checks if the `check` value is outside of the range of `low` to `high` inclusive. This is equivalent to `(check < low OR 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 out of range, a `0` (false) when it is 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 `0`.

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

EXAMPLES

```SELECT 5 NOT BETWEEN 2 AND 5, 5 NOT BETWEEN 5 AND 2;
```
```+-----------------------+-----------------------+
| 5 NOT BETWEEN 2 AND 5 | 5 NOT BETWEEN 5 AND 2 |
+-----------------------+-----------------------+
|                     0 |                     1 |
+-----------------------+-----------------------+
```
```CREATE TABLE not_between_example (
v1 INT,
v2 INT
);
```
```INSERT INTO not_between_example VALUES
(0, 3), (1, 4), (2, 5), (3, 6), (4, 7),
(5, 8), (6, 9), (7, 1), (8, 0);
```
```SELECT v1
FROM not_between_example
WHERE v1 NOT BETWEEN 2 AND 6;
```
```+------+
| v1   |
+------+
|    0 |
|    1 |
|    7 |
|    8 |
+------+
```
```SELECT CONCAT('!(', v1, ' <= 4 <= ', v2, ')') AS checking,
4 BETWEEN v1 AND v2 AS result
FROM not_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.