What is the MySQL BETWEEN Operator?
The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. The BETWEEN operator is inclusive: begin and end values are included.
- It is used to retrieve records based on the given range
- The MySQL BETWEEN operator can be used with SELECT, DELETE, UPDATE and INSERT statements.
- The two values provided in the BETWEEN operator are inclusive. For example, BETWEEN 10 and 20 means retrieving records that fall between these two values including 10 and 20.
- You may use numbers, dates and text values in the BETWEEN operator.
- The BETWEEN operator is used with the AND operator (as shown in the examples in the coming section).
- The BETWEEN is equivalent to (min <= expr AND expr <= max).
BETWEEN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;What is the use of between operator in MySQL?
The MySQL BETWEEN operator can be used with SELECT, DELETE, UPDATE and INSERT statements. The two values provided in the BETWEEN operator are inclusive. For example, BETWEEN 10 and 20 means retrieving records that fall between these two values including 10 and 20.
Demo Database
Below is a selection from the “Products” table in the Northwind sample database:
| ProductID | ProductName | SupplierID | CategoryID | Unit | Price |
|---|---|---|---|---|---|
| 1 | Chais | 1 | 1 | 10 boxes x 20 bags | 18 |
| 2 | Chang | 1 | 1 | 24 – 12 oz bottles | 19 |
| 3 | Aniseed Syrup | 1 | 2 | 12 – 550 ml bottles | 10 |
| 4 | Chef Anton’s Cajun Seasoning | 1 | 2 | 48 – 6 oz jars | 22 |
| 5 | Chef Anton’s Gumbo Mix | 1 | 2 | 36 boxes | 21.35 |
BETWEEN Example
The following SQL statement selects all products with a price between 10 and 20:
Example
SELECT * FROM Products<br>WHERE Price BETWEEN 10 AND 20;
NOT BETWEEN Example
To display the products outside the range of the previous example, use NOT BETWEEN:
Example
SELECT * FROM Products<br>WHERE Price NOT BETWEEN 10 AND 20;
BETWEEN with IN Example
The following SQL statement selects all products with a price between 10 and 20. In addition; do not show products with a CategoryID of 1,2, or 3:
Example
SELECT * FROM Products<br>WHERE Price BETWEEN 10 AND 20<br>AND CategoryID NOT IN (1,2,3);
BETWEEN Text Values Example
The following SQL statement selects all products with a ProductName between “Carnarvon Tigers” and “Mozzarella di Giovanni”:
Example
SELECT * FROM Products<br>WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'<br>ORDER BY ProductName;
The following SQL statement selects all products with a ProductName between “Carnarvon Tigers” and “Chef Anton’s Cajun Seasoning”:
Example
SELECT * FROM Products<br>WHERE ProductName BETWEEN "Carnarvon Tigers" AND "Chef Anton's Cajun Seasoning"<br>ORDER BY ProductName;
NOT BETWEEN Text Values Example
The following SQL statement selects all products with a ProductName not between “Carnarvon Tigers” and “Mozzarella di Giovanni”:
Example
SELECT * FROM Products<br>WHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'<br>ORDER BY ProductName;
Sample Table
Below is a selection from the “Orders” table in the Northwind sample database:
| OrderID | CustomerID | EmployeeID | OrderDate | ShipperID |
|---|---|---|---|---|
| 10248 | 90 | 5 | 7/4/1996 | 3 |
| 10249 | 81 | 6 | 7/5/1996 | 1 |
| 10250 | 34 | 4 | 7/8/1996 | 2 |
| 10251 | 84 | 3 | 7/9/1996 | 1 |
| 10252 | 76 | 4 | 7/10/1996 | 2 |
BETWEEN Dates Example
The following SQL statement selects all orders with an OrderDate between ’01-July-1996′ and ’31-July-1996′:
Example
SELECT * FROM Orders<br>WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';
Examples of the MySQL BETWEEN Operator
Consider the following Students table.
1. When Range Is Numeric
| 1 | SELECT * FROM Students WHERE DaysPresent>=60 AND DaysPresent<=80; |
The output we get is:
Now let us do this using the BETWEEN operator. We do this using the following query:
| 1 | SELECT * FROM Students WHERE DaysPresent BETWEEN 60 AND 80; |
We get the output as:
As you can see, using the BETWEEN operator makes your code more readable and less complex.
2. When Range Is A Text Value
The BETWEEN operator is not just limited to numeric values. We can also specify text values in the range. Let us see how that works. How about finding records of those students who have their City name in the range between Aurangabad and Mumbai. The query will be like,
| 1 | SELECT * FROM Students WHERE City BETWEEN 'Aurangabad' AND 'Mumbai'; |
Before we move on to the output, let us take a look at what this would look like. The range we have stated is between Aurangabad and Mumbai.
So anything, that has a value alphabetically greater than Mumbai in the column, will get omitted from the result-set. That means we should not have the cities of Nagpur and Pune in the result-set and their corresponding student records.
3. When Range Is A Date
Consider the following Employee table.
Let us find out which employees joined the company between 1st October 2018 and 1st October 2020. We use the following query for the same.
| 1 | SELECT * FROM Employee WHERE Date_Joined BETWEEN ‘2018-10-01’ AND ‘2020-10-01’; |
Alternatively, since some SQL databases may not support the above, you may also use,
| 1 | SELECT * FROM Employee WHERE Date_Joined BETWEEN CAST(‘2018-10-01’ AS DATE) AND CAST(‘2020-10-01’ AS DATE); |
You get the output as below,
