NOT BETWEEN
This page is part of MariaDB's Documentation.
The parent of this page is: SQL Operators for MariaDB Xpand
Topics on this page:
Overview
Negated inclusion in a range.
USAGE
check NOT BETWEEN low AND high
Value Name | Description |
|---|---|
| Value expressions |
| Value expressions |
| 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 |
+----------------+--------+