BETWEEN Operator in MySQL
Introduction
Range filtering is essential for dates, scores, ages, and measurements.The
BETWEEN operator in MySQL tests if a value lies within an inclusive range.
It is widely used for:
- Date range queries
- Score/age/price ranges
- Numeric interval filtering
What is the BETWEEN Operator
BETWEEN returns TRUE if value is greater than or equal to start AND less than or equal to end.
Inclusive: value >= min AND value <= max
Basic Syntax
sql
SELECT column_name
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Example
sql
SELECT * FROM Students
WHERE marks BETWEEN 70 AND 90;
Returns students with marks 70 to 90 inclusive.
BETWEEN with Numbers
Perfect for numeric ranges.
sql
SELECT * FROM Students
WHERE age BETWEEN 18 AND 25;
BETWEEN with Dates
Date range filtering.
sql
SELECT * FROM Students
WHERE join_date BETWEEN '2025-01-01' AND '2025-12-31';
BETWEEN with Strings
Alphabetical range (collation-based).
sql
SELECT * FROM Students
WHERE name BETWEEN 'A' AND 'M';
Names from A to M inclusive.
NOT BETWEEN
Excludes the range entirely.
sql
SELECT * FROM Students
WHERE marks NOT BETWEEN 40 AND 60;
Students below 40 or above 60.
BETWEEN with Multiple Conditions
Combine ranges.
sql
SELECT * FROM Students
WHERE marks BETWEEN 75 AND 100
AND age BETWEEN 18 AND 22;
BETWEEN with ORDER BY
Range filter, then sort.
sql
SELECT * FROM Students
WHERE marks BETWEEN 80 AND 95
ORDER BY marks DESC;
BETWEEN with GROUP BY
Range-based aggregation.
sql
SELECT city, COUNT(*)
FROM Students
WHERE age BETWEEN 18 AND 25
GROUP BY city;
BETWEEN with JOIN
Range conditions across tables.
sql
SELECT s.name, c.duration
FROM Students s
JOIN Courses c ON s.course_id = c.id
WHERE c.duration BETWEEN 3 AND 12;
BETWEEN with NULL
NULL BETWEEN returns NULL.
sql
SELECT * FROM Students
WHERE marks BETWEEN 0 AND 100; -- Excludes NULL marks
BETWEEN with Subqueries
Dynamic range endpoints.
sql
SELECT * FROM Students
WHERE marks BETWEEN (
SELECT AVG(marks)*0.8 FROM Students
) AND (
SELECT AVG(marks)*1.2 FROM Students
);
BETWEEN Performance
Uses indexes effectively.
sql
-- Index-friendly
WHERE marks BETWEEN 70 AND 90;
-- Equivalent but less readable
WHERE marks >= 70 AND marks <= 90;
String BETWEEN Collation
Depends onthe table collation.
sql
-- utf8mb4_general_ci (case-insensitive)
WHERE name BETWEEN 'apple' AND 'banana'; -- Matches Apple, BANANA
Execution Order with BETWEEN
Evaluated in WHERE after FROM.
FROM → WHERE (BETWEEN) → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
Performance Considerations
- BETWEEN uses indexes (range scan)
- Inclusive endpoints (good for most cases)
- Multiple BETWEENs: ensure index order
- String BETWEEN collation-dependent
Important Notes
- BETWEEN is inclusive (>= AND <=)
- NULL BETWEEN a AND b = NULL
- String comparison uses collation rules
- Date BETWEEN works with DATE, DATETIME
- Works with numbers, strings, dates, timestamps
Example Scenario
Find students in the prime age range with good marks:
sql
SELECT name, age, marks, city
FROM Students
WHERE age BETWEEN 18 AND 24
AND marks BETWEEN 75 AND 100
AND join_date BETWEEN '2025-06-01' AND CURDATE()
ORDER BY marks DESC, age
LIMIT 50;
Common Mistakes
- Expecting exclusive endpoints (use >/<)
- NULL handling surprise
- String collation case sensitivity
- BETWEEN with functions (prevents index use)
- Wrong endpoint order (a > b = never true)
Key Points to Remember
- BETWEEN is inclusive (min <= value <= max)
- Uses indexes effectively (range scan)
- Works with numbers, dates, strings
- NULL BETWEEN always NULL
- NOT BETWEEN excludes the entire range
- Collation affects string BETWEEN results