In SQL, the BETWEEN operator allows you to check for values that are within a given range.
The BETWEEN operator specifies a range to test, and it returns TRUE when the operand is within the range of comparisons.
Source Table
The following table is used for the examples on this page.
SELECT * FROM Pets;Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 1 | 2 | 3 | Fluffy | 2020-11-20 | | 2 | 3 | 3 | Fetch | 2019-08-16 | | 3 | 2 | 2 | Scratch | 2018-10-01 | | 4 | 3 | 3 | Wag | 2020-03-15 | | 5 | 1 | 1 | Tweet | 2020-11-28 | | 6 | 3 | 4 | Fluffy | 2020-09-17 | | 7 | 3 | 2 | Bark | NULL | | 8 | 2 | 4 | Meow | NULL | +---------+-------------+-----------+-----------+------------+ (8 rows affected)
Example
Here’s a simple example to demonstrate the BETWEEN operator.
SELECT * FROM Pets
WHERE DOB BETWEEN '2018-10-01' AND '2020-09-17';Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 2 | 3 | 3 | Fetch | 2019-08-16 | | 3 | 2 | 2 | Scratch | 2018-10-01 | | 4 | 3 | 3 | Wag | 2020-03-15 | | 6 | 3 | 4 | Fluffy | 2020-09-17 | +---------+-------------+-----------+-----------+------------+
In this example, we returned all rows where the DOB column was between 2018-10-01 and 2020-09-17.
In this case, four rows matched that criteria, and therefore, four rows were returned.
BETWEEN is Inclusive
The BETWEEN operator. is inclusive. That is, it includes the values at each end of the range. If you look at the results in the previous example, you’ll see that there are values on both ends that match the dates we provided exactly.
It’s the same as using the greater than or equal to operator (>=) combined with the less than or equal to operator (<=).
SELECT * FROM Pets
WHERE DOB >= '2018-10-01' AND DOB <= '2020-09-17';Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 2 | 3 | 3 | Fetch | 2019-08-16 | | 3 | 2 | 2 | Scratch | 2018-10-01 | | 4 | 3 | 3 | Wag | 2020-03-15 | | 6 | 3 | 4 | Fluffy | 2020-09-17 | +---------+-------------+-----------+-----------+------------+
To make it an exclusive range, use the greater than (>) operator and less than (<) operator.
SELECT * FROM Pets
WHERE DOB > '2018-10-01' AND DOB < '2020-09-17';Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 2 | 3 | 3 | Fetch | 2019-08-16 | | 4 | 3 | 3 | Wag | 2020-03-15 | +---------+-------------+-----------+-----------+------------+
If you must use the BETWEEN operator, you’ll need to narrow the range accordingly.
Like this:
SELECT * FROM Pets
WHERE DOB BETWEEN '2018-10-02' AND '2020-09-16';Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 2 | 3 | 3 | Fetch | 2019-08-16 | | 4 | 3 | 3 | Wag | 2020-03-15 | +---------+-------------+-----------+-----------+------------+
Using Numeric Values
The previous examples use dates, but you’re not limited to just dates. Here’s an example that uses numeric values.
SELECT * FROM Pets
WHERE OwnerId BETWEEN 2 AND 3;Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 1 | 2 | 3 | Fluffy | 2020-11-20 | | 2 | 3 | 3 | Fetch | 2019-08-16 | | 3 | 2 | 2 | Scratch | 2018-10-01 | | 4 | 3 | 3 | Wag | 2020-03-15 | | 7 | 3 | 2 | Bark | NULL | +---------+-------------+-----------+-----------+------------+
Using String Values
We could take it a step further and use BETWEEN to compare two string columns.
SELECT * FROM Pets
WHERE PetName BETWEEN 'Meow' AND 'Wag'
ORDER BY PetName;Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 8 | 2 | 4 | Meow | NULL | | 3 | 2 | 2 | Scratch | 2018-10-01 | | 5 | 1 | 1 | Tweet | 2020-11-28 | | 4 | 3 | 3 | Wag | 2020-03-15 | +---------+-------------+-----------+-----------+------------+
You do need to be careful though. You might think that we could just use the initial letter, like this:
SELECT * FROM Pets
WHERE PetName BETWEEN 'M' AND 'W'
ORDER BY PetName;But that will result in this:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 8 | 2 | 4 | Meow | NULL | | 3 | 2 | 2 | Scratch | 2018-10-01 | | 5 | 1 | 1 | Tweet | 2020-11-28 | +---------+-------------+-----------+-----------+------------+
It didn’t include Wag like the previous query did. That’s because BETWEEN looks at the whole string. In that case, Wag is greater than W, and so it is not included in the results.