BETWEEN

USAGE

check BETWEEN low AND high

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