MySQL BETWEEN Operator

MySQL Tutorial

MySQL BETWEEN Operator

The BETWEEN operator in MySQL is used in a WHERE clause to filter the result set within a specific range of values. It can be used with numbers, dates, or text.

✅ Syntax


  • Returns all rows where the column value is greater than or equal to value1 and less than or equal to value2.

  • Inclusive of both ends (value1 and value2).

🧠 Example Table: students

id name age marks
1 John 20 85
2 Emma 21 90
3 Raj 22 76
4 Arjun 23 92
5 Sara 19 88

✅ Example 1: Numbers (Range of Ages)


✔ Returns students with age 20, 21, or 22:

id name age marks
1 John 20 85
2 Emma 21 90
3 Raj 22 76

✅ Example 2: Numbers (Range of Marks)


✔ Returns students with marks between 80 and 90 (inclusive):

id name marks
1 John 85
2 Emma 90
5 Sara 88

✅ Example 3: Dates

Assume a table orders:

id order_date amount
1 2025-11-01 500
2 2025-11-05 700
3 2025-11-10 300
4 2025-11-15 900

✔ Returns orders on Nov 5 and Nov 10 as well as dates in between.

✅ Example 4: NOT BETWEEN

You can exclude values outside a range:


✔ Returns students younger than 20 or older than 22:

id name age
4 Arjun 23
5 Sara 19

🏁 Summary

Operator Purpose
BETWEEN Select values within a range (inclusive)
NOT BETWEEN Select values outside a range

You may also like...