> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# MySQL BETWEEN

> How the MySQL BETWEEN operator tests a range, why both bounds are included, why the low bound must come first, and the date range it silently gets wrong.

<Card title="VillageSQL is a drop-in replacement for MySQL with extensions." icon="database" href="/docs/mysql-8.4/stable/quickstart">
  All examples on this page work on VillageSQL. Install Now →
</Card>

`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

```sql theme={null}
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

```sql theme={null}
SELECT title, length
FROM film
WHERE length BETWEEN 60 AND 70
ORDER BY length, title
LIMIT 5;
```

```text theme={null}
+-----------------------+--------+
| 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

```sql theme={null}
SELECT title, length
FROM film
WHERE length >= 60 AND length <= 70
ORDER BY length, title
LIMIT 5;
```

```text theme={null}
+-----------------------+--------+
| 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

```sql theme={null}
SELECT title, length
FROM film
WHERE length NOT BETWEEN 60 AND 70
ORDER BY length, title
LIMIT 5;
```

```text theme={null}
+---------------------+--------+
| 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.

```sql theme={null}
SELECT title, length
FROM film
WHERE length BETWEEN 70 AND 60
ORDER BY length, title;
```

```text theme={null}
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.

```sql theme={null}
SHOW COLUMNS FROM payment LIKE 'payment_date';
```

```text theme={null}
+--------------+----------+------+-----+---------+-------+
| 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.

```sql theme={null}
SELECT payment_id
FROM payment
WHERE payment_date BETWEEN '2005-05-25' AND '2005-05-26'
ORDER BY payment_id;
```

```text theme={null}
+------------+
| 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.

```sql theme={null}
SELECT payment_id
FROM payment
WHERE payment_date >= '2005-05-25' AND payment_date < '2005-05-27'
ORDER BY payment_id;
```

```text theme={null}
+------------+
| 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`.

## See also

* [WHERE](/docs/tutorial/where) — the clause BETWEEN goes in
* [Operators](/docs/tutorial/operators) — the comparisons BETWEEN is shorthand for
* [IN](/docs/tutorial/in) — testing a list of values rather than a range
* [Date and time functions in MySQL](/docs/guides/date-time-functions) — building the bounds of a range