MySQL BETWEEN Operator

In this article, you will learn everything about MySQL BETWEEN operator with the help of examples. In the previous tutorial, we have seen all about IN operators with the help of examples.

If you don’t know about MySQL BETWEEN operator, At the end of this article, you are completely able to use BETWEEN operator in MySQL query to select data within a specific range.

Introdution of MySQL BETWEEN Operator

BETWEEN operator in MySQL is used to select data within a given range. The value can be number, text, dates, etc.BETWEEN operator in MySQL play an important role when you want to select records from the database table whose match condition is in a given range.

Note:- MySQL BETWEEN operator is inclusive which means start and end value include in result set.

Syntax

The syntax of MySQL BETWEEN operator is:-

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;

Demo Database

We have sample table students with the following records, we will use this table throughout the tutorial.

Output

+-------+------------+-----------+--------+---------------------+---------+
| st_id | first_name | last_name | course | created_at          | roll_no |
+-------+------------+-----------+--------+---------------------+---------+
|     1 | Vishvajit  | Rao       | MCA    | 2021-11-13 14:26:39 |      10 |
|     2 | John       | Doe       | Mtech  | 2021-11-13 14:26:39 |      19 |
|     3 | Shivam     | Kumar     | B.A.   | 2021-11-13 14:26:39 |      25 |
|     4 | Pankaj     | Singh     | Btech  | 2021-11-13 14:54:28 |      12 |
|     5 | Hayati     | Kaur      | LLB    | 2021-11-13 14:54:28 |      40 |
|     6 | Aysha      | Garima    | BCA    | 2021-11-13 14:54:28 |      26 |
|     7 | Abhi       | Kumar     | MCA    | 2021-11-28 11:43:40 |      23 |
|     8 | Kartike    | Singh     | Btech  | 2021-11-28 11:44:22 |      17 |
+-------+------------+-----------+--------+---------------------+---------+

MySQL BETWEEN Operator Examples

We have seen various ways to the use BETWEEN operator in MySQL.

a). BETWEEN Example:

The following query selects all the students with roll_no between 15 and 30.

SELECT * FROM students WHERE roll_no BETWEEN 15 AND 30;

Output

+-------+------------+-----------+--------+---------------------+---------+
| st_id | first_name | last_name | course | created_at          | roll_no |
+-------+------------+-----------+--------+---------------------+---------+
|     2 | John       | Doe       | Mtech  | 2021-11-13 14:26:39 |      19 |
|     3 | Shivam     | Kumar     | B.A.   | 2021-11-13 14:26:39 |      25 |
|     6 | Aysha      | Garima    | BCA    | 2021-11-13 14:54:28 |      26 |
|     7 | Abhi       | Kumar     | MCA    | 2021-11-28 11:43:40 |      23 |
|     8 | Kartike    | Singh     | Btech  | 2021-11-28 11:44:22 |      17 |
+-------+------------+-----------+--------+---------------------+---------+

b). NOT BETWEEN Example:

The following query selects all the students where roll_no is not equal to between 15 and 30.

SELECT * FROM students WHERE roll_no NOT BETWEEN 15 AND 30;

Output

+-------+------------+-----------+--------+---------------------+---------+
| st_id | first_name | last_name | course | created_at          | roll_no |
+-------+------------+-----------+--------+---------------------+---------+
|     1 | Vishvajit  | Rao       | MCA    | 2021-11-13 14:26:39 |      10 |
|     4 | Pankaj     | Singh     | Btech  | 2021-11-13 14:54:28 |      12 |
|     5 | Hayati     | Kaur      | LLB    | 2021-11-13 14:54:28 |      40 |
+-------+------------+-----------+--------+---------------------+---------+

c). BETWEEN with IN Example:

We want to select all the students with roll_no between 15 and 30 and st_id is 1, 2, 3, 5, or 8.

Output

SELECT * FROM students WHERE roll_no BETWEEN 15 AND 30 AND st_id IN (1,2,3,5,8);

Output

+-------+------------+-----------+--------+---------------------+---------+
| st_id | first_name | last_name | course | created_at          | roll_no |
+-------+------------+-----------+--------+---------------------+---------+
|     2 | John       | Doe       | Mtech  | 2021-11-13 14:26:39 |      19 |
|     3 | Shivam     | Kumar     | B.A.   | 2021-11-13 14:26:39 |      25 |
|     8 | Kartike    | Singh     | Btech  | 2021-11-28 11:44:22 |      17 |
+-------+------------+-----------+--------+---------------------+---------+

d). Using MySQL BETWEEN with Date Example:

SELECT * FROM students WHERE created_at BETWEEN '2021-11-13' AND '2021-11-28';

Output

+-------+------------+-----------+--------+---------------------+---------+
| st_id | first_name | last_name | course | created_at          | roll_no |
+-------+------------+-----------+--------+---------------------+---------+
|     1 | Vishvajit  | Rao       | MCA    | 2021-11-13 14:26:39 |      10 |
|     2 | John       | Doe       | Mtech  | 2021-11-13 14:26:39 |      19 |
|     3 | Shivam     | Kumar     | B.A.   | 2021-11-13 14:26:39 |      25 |
|     4 | Pankaj     | Singh     | Btech  | 2021-11-13 14:54:28 |      12 |
|     5 | Hayati     | Kaur      | LLB    | 2021-11-13 14:54:28 |      40 |
|     6 | Aysha      | Garima    | BCA    | 2021-11-13 14:54:28 |      26 |
+-------+------------+-----------+--------+---------------------+---------+

Conclusion

In this article, we have seen all about MySQL BETWEEN operator with the help of the examples.BETWEEN in MySQL is one of the most useful operators and most used by developers to select records within a given range of values.

I hope this article will help you. If you like this article, please share and keep visiting for further MySQL tutorials.

---

Frequently Asked Questions

Thanks for your valuable time …..

Reference:- Click Here