In this tutorial, we’ll learn about MySQL BETWEEN operator. Suppose you are a school teacher and you want to find out the students who scored marks between 60 and 80 in Science. You could make use of the comparison operators along with the AND operator but a more effective way is using the MySQL
The MySQL BETWEEN operator is used to retrieve values from a given range of values. The BETWEEN operator is used along with the WHERE clause.
Syntax of the MySQL BETWEEN Operator
SELECT expressions FROM table_name WHERE column_name BETWEEN value1 AND value2;Code language: SQL (Structured Query Language) (sql)
Here, value1 and value2 define the inclusive range of column_name values that should be returned as output. What that means is value1 and value2 are both included in that range.
Examples of the MySQL BETWEEN Operator
Consider the following Students table.
1. When Range Is Numeric
How about finding the students who were present for 60 to 80 days? Without using the
BETWEEN operator, we do it using the following query –
SELECT * FROM Students WHERE DaysPresent>=60 AND DaysPresent<=80;Code language: SQL (Structured Query Language) (sql)
The output we get is:
Now let us do this using the
BETWEEN operator. We do this using the following query:
SELECT * FROM Students WHERE DaysPresent BETWEEN 60 AND 80;Code language: SQL (Structured Query Language) (sql)
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,
SELECT * FROM Students WHERE City BETWEEN 'Aurangabad' AND 'Mumbai';Code language: SQL (Structured Query Language) (sql)
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.
So, the output we get is,
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.
SELECT * FROM Employee WHERE Date_Joined BETWEEN ‘2018-10-01’ AND ‘2020-10-01’;Code language: SQL (Structured Query Language) (sql)
Alternatively, since some SQL databases may not support the above, you may also use,
SELECT * FROM Employee WHERE Date_Joined BETWEEN CAST(‘2018-10-01’ AS DATE) AND CAST(‘2020-10-01’ AS DATE);Code language: SQL (Structured Query Language) (sql)
You get the output as below,
As seen above, the
BETWEEN operator can be very useful to get data from a range of values. I would highly recommend you to check the below links for further reading.
- MySQL official documentation on the
- JournalDev article on the