MariaDB BETWEEN: Inclusive Ranges, NOT BETWEEN and Dates

MariaDB BETWEEN keeps rows whose value sits inside a range, and both ends count: amount BETWEEN 50 AND 200 means amount >= 50 AND amount <= 200. One date detail quietly drops rows, though. Here’s the basic query:

SELECT id, customer, amount
FROM orders
WHERE amount BETWEEN 50 AND 200;

Four rows back:

+----+----------------+--------+
| id | customer       | amount |
+----+----------------+--------+
|  2 | Tyler Reed     |  50.00 |
|  3 | Grace Miller   | 120.00 |
|  4 | Marcus Bennett | 200.00 |
|  5 | Daniel Hayes   |  75.25 |
+----+----------------+--------+

Orders 2 and 4 sit exactly on 50.00 and 200.00, and both still come back. Tested on MariaDB 13.0.2 on Windows.

Sample Table for BETWEEN

You’ll reuse these seven orders. Watch 49.99, 200.01, the NULL and both March 31 times:

DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer VARCHAR(16) NOT NULL,
  state CHAR(2) NOT NULL,
  amount DECIMAL(8,2) NULL,
  placed_at DATETIME NOT NULL,
  KEY ix_placed (placed_at)
);
INSERT INTO orders VALUES
(1, 'Hannah Brooks',  'OH',  49.99, '2026-03-01 08:30:00'),
(2, 'Tyler Reed',     'TX',  50.00, '2026-03-09 12:10:00'),
(3, 'Grace Miller',   'CA', 120.00, '2026-03-15 16:45:00'),
(4, 'Marcus Bennett', 'TX', 200.00, '2026-03-22 09:05:00'),
(5, 'Daniel Hayes',   'GA',  75.25, '2026-03-31 00:00:00'),
(6, 'Olivia Turner',  'CA', 200.01, '2026-03-31 14:00:00'),
(7, 'Caleb Foster',   'NY',   NULL, '2026-04-01 10:20:00');

Is MariaDB BETWEEN Inclusive?

Yes. The docs define it as min <= expr AND expr <= max when the types match. Check your work: you should see 49.99 and 200.01 left out.

For an exclusive range, write > and <. Mixed types get converted, so 2 BETWEEN 2 AND '3' returns 1 (MariaDB CAST controls that).

MariaDB NOT BETWEEN and NULL Values

NOT BETWEEN flips the test:

SELECT id, amount FROM orders
WHERE amount NOT BETWEEN 50 AND 200;

Only the two outside amounts:

+----+--------+
| id | amount |
+----+--------+
|  1 |  49.99 |
|  6 | 200.01 |
+----+--------+

Order 7 (NULL amount) is in neither result. A comparison with NULL is unknown rather than false, so neither the range nor its opposite matches. Add OR amount IS NULL if you need it, or swap in a default with MariaDB IFNULL.

MariaDB BETWEEN Dates and the DATETIME End-of-Day Trap

On a DATETIME column, '2026-03-31' means midnight. Orders 4 to 6:

-- BETWEEN
SELECT id, placed_at FROM orders
WHERE placed_at BETWEEN '2026-03-01' AND '2026-03-31'
  AND id >= 4;

-- half-open
SELECT id, placed_at FROM orders
WHERE placed_at >= '2026-03-01'
  AND placed_at <  '2026-04-01'
  AND id >= 4;

Compare the two results:

+----+---------------------+
| id | placed_at           |
+----+---------------------+
|  4 | 2026-03-22 09:05:00 |
|  5 | 2026-03-31 00:00:00 |
+----+---------------------+
+----+---------------------+
| id | placed_at           |
+----+---------------------+
|  4 | 2026-03-22 09:05:00 |
|  5 | 2026-03-31 00:00:00 |
|  6 | 2026-03-31 14:00:00 |
+----+---------------------+

BETWEEN kept Daniel’s order stamped exactly at midnight and lost Olivia’s 2:00 PM one that day. The half-open range (>= first day, < next day) caught both. Use it for DATETIME.

Plain DATE is safe. More in MariaDB date functions and the difference between two dates in MariaDB.

BETWEEN With Strings: Alphabetical Order and Collation

SELECT id, customer FROM orders
WHERE customer BETWEEN 'a' AND 'h';

Three names:

+----+--------------+
| id | customer     |
+----+--------------+
|  3 | Grace Miller |
|  5 | Daniel Hayes |
|  7 | Caleb Foster |
+----+--------------+

Lowercase bounds matched, because the default collation ignores case. Hannah is missing: ‘Hannah’ sorts after ‘h’.

Use < 'i' or MariaDB LIKE for initials.

BETWEEN in HAVING With GROUP BY and ORDER BY

BETWEEN filters totals in HAVING, after MariaDB GROUP BY:

SELECT state, SUM(amount) AS total
FROM orders
GROUP BY state
HAVING SUM(amount) BETWEEN 50 AND 300
ORDER BY total DESC;

Two states qualify:

+-------+--------+
| state | total  |
+-------+--------+
| TX    | 250.00 |
| GA    |  75.25 |
+-------+--------+

Ohio and California fall outside. MariaDB ORDER BY sorts the rest, and in a MariaDB JOIN BETWEEN works in ON too.

Does BETWEEN Use an Index?

Yes, as a range scan on ix_placed:

EXPLAIN SELECT id FROM orders
WHERE placed_at BETWEEN '2026-03-09' AND '2026-03-15';

The plan:

+------+-------------+--------+-------+---------------+-----------+---------+------+------+--------------------------+
| id   | select_type | table  | type  | possible_keys | key       | key_len | ref  | rows | Extra                    |
+------+-------------+--------+-------+---------------+-----------+---------+------+------+--------------------------+
|    1 | SIMPLE      | orders | range | ix_placed     | ix_placed | 5       | NULL | 1    | Using where; Using index |
+------+-------------+--------+-------+---------------+-----------+---------+------+------+--------------------------+

type is range, 1 row: order 3 at 4:45 PM on 3/15 is past the midnight bound again. More in MariaDB EXPLAIN.

BETWEEN Mistakes: Reversed Bounds and ERROR 1064

SELECT COUNT(*) AS reversed FROM orders
WHERE amount BETWEEN 200 AND 50;

SELECT id FROM orders WHERE amount BETWEEN 50, 200;

Two mistakes I hit building the demo:

+----------+
| reversed |
+----------+
|        0 |
+----------+
--------------
SELECT id FROM orders WHERE amount BETWEEN 50, 200
--------------

ERROR 1064 (42000) at line 4: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' 200' at line 1

• BETWEEN 200 AND 50 isn’t swapped for you, so it quietly matches nothing and you get no warning.
• A comma instead of AND is a syntax error (more in MariaDB ERROR 1064).
• NOT x BETWEEN equals NOT BETWEEN unless sql_mode has HIGH_NOT_PRECEDENCE: NOT 1 BETWEEN -5 AND 5 gave me 0, then 1.

Next Step After BETWEEN

Next step: sort the filtered rows with MariaDB ORDER BY DESC. Also:

• Create the index that makes range filters fast
• Filter MariaDB dates with greater than
• The same date range in PostgreSQL

Frequently Asked Questions