BETWEEN is shorthand for two comparisons joined with AND. It is a small
piece of syntax with one genuinely important gotcha attached, and the gotcha is about dates.
The basics
x BETWEEN a AND b means exactly x >= a AND x <= b —
inclusive at both ends:
SELECT p.name, ps.size, ps.price
FROM product_size ps JOIN product p ON p.id = ps.product_id
WHERE ps.price BETWEEN 15 AND 17
ORDER BY ps.price, p.name;+-----------------------+--------+-------+
| name | size | price |
+-----------------------+--------+-------+
| Cheese Pizza | LARGE | 15.49 |
| Hawaiian Pizza | MEDIUM | 15.49 |
| Veggie Lovers Pizza | MEDIUM | 15.99 |
| BBQ Chicken Pizza | MEDIUM | 16.99 |
| Buffalo Chicken Pizza | MEDIUM | 16.99 |
| Pepperoni Pizza | LARGE | 16.99 |
| Supreme Pizza | MEDIUM | 16.99 |
+-----------------------+--------+-------+The long form returns the same seven rows:
SELECT COUNT(*) AS in_range
FROM product_size
WHERE price >= 15 AND price <= 17;+----------+
| in_range |
+----------+
| 7 |
+----------+The lower bound must come first. BETWEEN 17 AND 15 is not an error and matches
nothing, which is a quiet way to get an empty report.
NOT BETWEEN
SELECT p.name, ps.price
FROM product_size ps JOIN product p ON p.id = ps.product_id
WHERE ps.size = 'LARGE' AND ps.price NOT BETWEEN 15 AND 19
ORDER BY ps.price;+-----------------------+-------+
| name | price |
+-----------------------+-------+
| Bottled Water | 2.49 |
| Pepsi | 2.99 |
| Diet Pepsi | 2.99 |
| Mountain Dew | 2.99 |
| Starry | 2.99 |
| Iced Tea | 2.99 |
| Supreme Pizza | 19.99 |
| BBQ Chicken Pizza | 19.99 |
| Buffalo Chicken Pizza | 19.99 |
| Meat Lovers Pizza | 20.99 |
+-----------------------+-------+NOT BETWEEN is < a OR > b. Note that a NULL price would appear
in neither result — BETWEEN is built from comparisons, so NULL stays unknown in both
directions. See NULL and IS NULL.
The date trap
This is the one to remember. customer_order.created_at is a
DATETIME(6) — a date and a time. Writing a bare date as the upper bound
means midnight:
-- '2024-01-31' as an upper bound means '2024-01-31 00:00:00'
SELECT CAST('2024-01-31 14:05:00' AS DATETIME) BETWEEN '2024-01-01' AND '2024-01-31' AS in_january,
CAST('2024-01-31 14:05:00' AS DATETIME) >= '2024-01-01'
AND CAST('2024-01-31 14:05:00' AS DATETIME) < '2024-02-01' AS half_open;+------------+-----------+
| in_january | half_open |
+------------+-----------+
| 0 | 1 |
+------------+-----------+An order placed at 2 pm on the 31st is not "between the 1st and the 31st".
BETWEEN '2024-01-01' AND '2024-01-31' silently drops all but the first instant of the
last day — very nearly a whole day of a monthly report, and the number still looks plausible.
The reliable fix is the half-open range: greater-or-equal at the start, strictly less-than at the exclusive end.
-- January, all of it, including 23:59:59.999999 on the 31st
SELECT COUNT(*) FROM customer_order
WHERE created_at >= '2024-01-01'
AND created_at < '2024-02-01';It reads slightly worse and it is correct. It also handles month lengths without you thinking
about them, and it keeps the column bare so an index on created_at can still be used
— which DATE(created_at) = '2024-01-31' would prevent. See
date functions.
If the column is a plain DATE with no time component, BETWEEN is safe.
The trap is specifically DATETIME and TIMESTAMP.
BETWEEN on strings
It works on text too, comparing by collation order:
SELECT name FROM product WHERE name BETWEEN 'B' AND 'D' ORDER BY name;Useful occasionally for alphabetical bucketing, and subject to the same off-by-one thinking:
'D' as an upper bound excludes 'Diet Pepsi', because
'Diet Pepsi' > 'D'.
What to remember
BETWEENis inclusive at both ends, and the low bound goes first.- On a
DATETIME, a bare date as the upper bound means midnight — use a half-open>= ... < ...range instead. - NULL is in neither
BETWEENnorNOT BETWEEN.