MySQL BETWEEN operator

The BETWEEN operator is used to test whether a value is between certain two values. It can be used to compare numeric and date types of data.

MySQL BETWEEN syntax

The BETWEEN operator is a ternary operator, and it requires 3 operands. The following is the syntax of the BETWEEN operator:

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 minimum value of the range (inclusive).
max is the maximum value of the range (inclusive).
AND is a fixed conjunction for connecting min and max.
NOT BETWEEN is negative operation ot BETWEEN.

The BETWEEN operator is equivalent to the following expression:

expression >= min AND expression <= max

The NOT BETWEEN operator is equivalent to the following expression:

expression < min OR expression > max

MySQL BETWEEN algorithm

The algorithm of the BETWEEN operator is as follows:

If three operands are not NULL and the BETWEEN operator returns the same result as expression >= min AND expression <= max.

SELECT
    1 BETWEEN 1 AND 3,
    2 BETWEEN 1 AND 3,
    3 BETWEEN 1 AND 3,
    4 BETWEEN 1 AND 3;

+-------------------+-------------------+-------------------+-------------------+
| 1 BETWEEN 1 AND 3 | 2 BETWEEN 1 AND 3 | 3 BETWEEN 1 AND 3 | 4 BETWEEN 1 AND 3 |
+-------------------+-------------------+-------------------+-------------------+
|                 1 |                 1 |                 1 |                 0 |
+-------------------+-------------------+-------------------+-------------------+

If one of the three operands expression, min, max is NULL, the BETWEEN operator returns NULL.

SELECT
    NULL BETWEEN 1 AND 3,
    1 BETWEEN NULL AND 3,
    1 BETWEEN 1 AND NULL;

+----------------------+----------------------+----------------------+
| NULL BETWEEN 1 AND 3 | 1 BETWEEN NULL AND 3 | 1 BETWEEN 1 AND NULL |
+----------------------+----------------------+----------------------+
|                 NULL |                 NULL |                 NULL |
+----------------------+----------------------+----------------------+

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 |
...