MySQL BETWEEN Operator

By /
Between Operator

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

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.

In Operator Students Table MySQL BETWEEN
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:

Without MySQL Between Example 1

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:

MySQL Between Example 1

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,

Between Example 2

3. When Range Is A Date

Consider the following Employee table.

Between Employee Table
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,

Between Example 3

Conclusion

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.

References