BETWEEN

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.

SYNONYMS

SCHEMA

PARAMETERS

SKYSQL

PRIVILEGES

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 |
+-------------+--------+

ERROR HANDLING

FEATURE INTERACTION

RESPONSES

DIAGNOSIS

ISO 9075:2016

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.

EXTERNAL REFERENCES