MySQL BETWEEN

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

BETWEEN tests whether a value falls in a range. It is shorthand for a pair of comparisons, and it is the clause people most often get subtly wrong on dates. Open the client with mysql -u root -p sakila before you start.

How BETWEEN works

SELECT COLUMN_LIST
FROM TABLE_NAME
WHERE COLUMN_NAME BETWEEN LOW_BOUND AND HIGH_BOUND;

Replace LOW_BOUND and HIGH_BOUND with the 2 ends of the range. x BETWEEN a AND b means x >= a AND x <= b. The range includes both bounds, and you write the low bound first.

Examples

1) A range of numbers

SELECT title, length
FROM film
WHERE length BETWEEN 60 AND 70
ORDER BY length, title
LIMIT 5;

+-----------------------+--------+
| title                 | length |
+-----------------------+--------+
| BUBBLE GROSSE         |     60 |
| JERSEY SASSY          |     60 |
| MOCKINGBIRD HOLLYWOOD |     60 |
| PHANTOM GLORY         |     60 |
| PITY BOUND            |     60 |
+-----------------------+--------+
5 rows in set (0.00 sec)

Films of exactly 60 minutes are in the result, which shows that the low bound counts as a match.

2) The same thing written out

SELECT title, length
FROM film
WHERE length >= 60 AND length <= 70
ORDER BY length, title
LIMIT 5;

+-----------------------+--------+
| title                 | length |
+-----------------------+--------+
| BUBBLE GROSSE         |     60 |
| JERSEY SASSY          |     60 |
| MOCKINGBIRD HOLLYWOOD |     60 |
| PHANTOM GLORY         |     60 |
| PITY BOUND            |     60 |
+-----------------------+--------+
5 rows in set (0.00 sec)

Identical rows. BETWEEN saves you naming the column twice and nothing else.

3) Outside the range

SELECT title, length
FROM film
WHERE length NOT BETWEEN 60 AND 70
ORDER BY length, title
LIMIT 5;

+---------------------+--------+
| title               | length |
+---------------------+--------+
| ALIEN CENTER        |     46 |
| IRON MOON           |     46 |
| KWAI HOMEWARD       |     46 |
| LABYRINTH LEAGUE    |     46 |
| RIDGEMONT SUBMARINE |     46 |
+---------------------+--------+
5 rows in set (0.01 sec)

NOT BETWEEN keeps rows below the low bound and above the high bound. 46 minutes is the shortest film in the catalog.

The low bound has to come first

BETWEEN does not sort your bounds for you. Give it a high value first and it asks for numbers that are both at least 70 and at most 60, which no number is.

SELECT title, length
FROM film
WHERE length BETWEEN 70 AND 60
ORDER BY length, title;

Empty set (0.00 sec)

No error and no warning. An empty result from a range you expected to match is worth checking for swapped bounds first.

BETWEEN on dates stops at midnight

This is the one that costs people real time, and it starts with the column’s type.

SHOW COLUMNS FROM payment LIKE 'payment_date';

+--------------+----------+------+-----+---------+-------+
| Field        | Type     | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+-------+
| payment_date | datetime | NO   |     | NULL    |       |
+--------------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)

payment_date is a datetime, so every value carries a time of day. A date written without a time means midnight at the start of that day.

SELECT payment_id
FROM payment
WHERE payment_date BETWEEN '2005-05-25' AND '2005-05-26'
ORDER BY payment_id;

+------------+
| payment_id |
+------------+
|          1 |
|        146 |
|        174 |
...
+------------+
137 rows in set (0.01 sec)

That reads as “the 25th and the 26th” and returns 137 rows. Asking for the same 2 days as a half-open range returns 311.

SELECT payment_id
FROM payment
WHERE payment_date >= '2005-05-25' AND payment_date < '2005-05-27'
ORDER BY payment_id;

+------------+
| payment_id |
+------------+
|          1 |
|        146 |
|        174 |
...
+------------+
311 rows in set (0.00 sec)

The high bound '2005-05-26' became 2005-05-26 00:00:00. Every payment taken during the 26th is later than that instant, so BETWEEN excluded all of them. The 137 rows are the 25th on its own, and they would have included a payment landing exactly on the stroke of midnight if one existed. Write a date range as >= start AND < the day after the end. That form needs no thought about whether the column carries a time, and it stays correct if the column later gains one.

Summary

Use BETWEEN for a range, and expect it to include both bounds.
Write the low bound first, because a reversed range matches nothing and says nothing.
Read x BETWEEN a AND b as x >= a AND x <= b.
Use >= start AND < day_after_end for dates rather than BETWEEN.

Getting Started

Server Development

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

How BETWEEN works

Examples

1) A range of numbers

2) The same thing written out

3) Outside the range

The low bound has to come first

BETWEEN on dates stops at midnight

Summary

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​How BETWEEN works

​Examples

​1) A range of numbers

​2) The same thing written out

​3) Outside the range

​The low bound has to come first

​BETWEEN on dates stops at midnight

​Summary

​See also

How BETWEEN works

Examples

1) A range of numbers

2) The same thing written out

3) Outside the range

The low bound has to come first

BETWEEN on dates stops at midnight

Summary

See also