MySQL BETWEEN operator

The BETWEEN operator tests whether a value falls within two bounds. Both endpoints are included, so a value equal to either bound matches.

MySQL BETWEEN syntax

BETWEEN takes three operands: an expression and a lower and upper bound. Its syntax is:

expression BETWEEN min AND max
expression NOT BETWEEN min AND max

Here:

• expression can be a column name, value, or an expression (such as function calls).
• min is the lower bound of the range (inclusive).
• max is the upper bound of the range (inclusive).
• AND is a fixed conjunction for connecting min and max.
• NOT BETWEEN is the logical negation of BETWEEN.

When all three operands have the same type, BETWEEN is equivalent to the following expression:

expression >= min AND expression <= max

For same-type operands, NOT BETWEEN is equivalent to the following expression:

expression < min OR expression > max

NULL values and type conversion

MySQL applies type-conversion rules to all three operands. If they have different types, do not assume the result is identical to separate pairwise comparisons. See the MySQL comparison-operator rules.

If a comparison is unknown because an operand is NULL, SQL’s three-valued logic applies. For example, a false comparison can make the whole range test false even when the other comparison is unknown:

SELECT 1 BETWEEN NULL AND 3 AS unknown_lower_bound,
       1 BETWEEN 2 AND NULL AS false_out_of_range,
       NULL BETWEEN 1 AND 3 AS unknown_expression;

+---------------------+---------------------+---------------------+
| unknown_lower_bound | false_out_of_range  | unknown_expression  |
+---------------------+---------------------+---------------------+
| NULL                | 0                   | NULL                |
+---------------------+---------------------+---------------------+

The NOT BETWEEN result is the negation of the BETWEEN result, so NOT BETWEEN also returns NULL when the original comparison is unknown.

Use BETWEEN with dates

For a DATE column, an inclusive range can select both calendar dates at its endpoints:

SELECT order_id, order_date
FROM orders
WHERE order_date BETWEEN DATE '2026-01-01' AND DATE '2026-01-31';

For a DATETIME column, a DATE bound converts to midnight. To include every time on January 31, use the next day as an exclusive upper bound:

SELECT order_id, created_at
FROM orders
WHERE created_at >= CAST('2026-01-01' AS DATETIME)
  AND created_at < CAST('2026-02-01' AS DATETIME);

MySQL documents this date/time conversion in the comparison-operator reference.

MySQL BETWEEN examples

The BETWEEN operator can be used for comparisons between numeric and date type data. Let’s look at practical examples.

In the following example, we use the the film table from Sakila sample database as a demonstration.

BETWEEN

The following SQL statement using the BETWEEN operator to retrieve films with replacement cost between price 1 and 10:

SELECT
    film_id, title, replacement_cost
FROM
    film
WHERE
    replacement_cost BETWEEN 1 AND 10;

+---------+------------------------+------------------+
| film_id | title                  | replacement_cost |
+---------+------------------------+------------------+
|      23 | ANACONDA CONFESSIONS   |             9.99 |
|     150 | CIDER DESIRE           |             9.99 |
|     182 | CONTROL ANTHEM         |             9.99 |
|     203 | DAISY MENAGERIE        |             9.99 |
|     221 | DELIVERANCE MULHOLLAND |             9.99 |
...

This statement is equivalent to the following statement:

SELECT
    film_id, title, replacement_cost
FROM
    film
WHERE
    replacement_cost >= 1 AND replacement_cost <= 10;

NOT BETWEEN

The following SQL statement is used to retrieve the films with replacement cost not between 1 and 10:

SELECT
    film_id, title, replacement_cost
FROM
    film
WHERE
    replacement_cost NOT BETWEEN 10 AND 50;

+---------+------------------------+------------------+
| film_id | title                  | replacement_cost |
+---------+------------------------+------------------+
|      23 | ANACONDA CONFESSIONS   |             9.99 |
|     150 | CIDER DESIRE           |             9.99 |
|     182 | CONTROL ANTHEM         |             9.99 |
|     203 | DAISY MENAGERIE        |             9.99 |
|     221 | DELIVERANCE MULHOLLAND |             9.99 |
...

This statement is equivalent to the following statement:

SELECT
    film_id, title, replacement_cost
FROM
    film
WHERE
    replacement_cost < 10 OR replacement_cost > 50;

Conclusion

This article described MySQL BETWEEN operator syntax and its usage. The main points of BETWEEN are as follows:

• BETWEEN is used to test whether a value is between certain two values.
• NOT BETWEEN is negative operation ot BETWEEN.
• BETWEEN can be used to compare numeric and date types of data.