MySQL BETWEEN Operator

The BETWEEN operator filters rows where a column value falls within a specific range — from a lower boundary to an upper boundary. It works with numbers, dates, and even text, and it is inclusive on both ends, meaning the boundary values themselves are included in the result.

---

Syntax

SELECT columns FROM table_name
WHERE column_name BETWEEN lower_value AND upper_value;

---

BETWEEN with Numbers

Find students whose age is between 21 and 23 (inclusive):

SELECT * FROM students
WHERE age BETWEEN 21 AND 23;

id	name	city	age
1	Rahim Uddin	Dhaka	22
3	Karim Ali	Sylhet	23
4	Nila Akter	Dhaka	21

Students aged 21, 22, and 23 are all included — both boundaries count.

---

BETWEEN with Dates

Date ranges are one of the most common uses of BETWEEN in real applications — for example, filtering orders placed within a specific month:

-- Orders placed in June 2025
SELECT * FROM orders
WHERE order_date BETWEEN '2025-06-01' AND '2025-06-30';

-- Users registered in 2024
SELECT * FROM users
WHERE registered_at BETWEEN '2024-01-01' AND '2024-12-31';

---

BETWEEN with Text

BETWEEN also works on text columns using alphabetical order:

-- Products with names alphabetically between 'C' and 'M'
SELECT name FROM products
WHERE name BETWEEN 'C' AND 'M';

This returns products whose names start with any letter from C to M. Text comparison follows the column's collation (sort order).

---

NOT BETWEEN

NOT BETWEEN returns rows where the value falls outside the specified range:

-- Students whose age is NOT between 21 and 23
SELECT * FROM students
WHERE age NOT BETWEEN 21 AND 23;

id	name	city	age
2	Sara Begum	Chittagong	20
5	Rafi Hossain	Rajshahi	24

---

BETWEEN vs >= and <=

Both queries below produce exactly the same result:

-- Using BETWEEN
SELECT * FROM students WHERE age BETWEEN 21 AND 23;

-- Equivalent with comparison operators
SELECT * FROM students WHERE age >= 21 AND age <= 23;

BETWEEN is simply a cleaner way to express the same range condition.

---

Key Points to Remember

• BETWEEN lower AND upper is inclusive — both boundary values are part of the result.
• It works with numbers, dates, and text columns.
• For DATETIME columns, be precise about the upper boundary to include the full last day.
• NOT BETWEEN returns rows outside the range.
• BETWEEN a AND b is equivalent to column >= a AND column <= b.
• Always put the smaller value first — BETWEEN 21 AND 23, not BETWEEN 23 AND 21.