LearnContact
Lesson 3715 min read

BETWEEN

Learn how the BETWEEN operator lets you filter rows within an inclusive range of numbers or dates.

Introduction

In the previous lessons, you learned comparison operators like >=, <=, and combined conditions with AND to build a range check, for example marks >= 60 AND marks <= 80. MySQL gives you a shorter, more readable way to write exactly that: the BETWEEN operator.

This lesson covers how BETWEEN works, why its bounds are inclusive, how it applies to both numbers and dates, and how to flip it around with NOT BETWEEN.

What You Will Learn
  • What the BETWEEN operator does and why it is inclusive on both ends.
  • How to use BETWEEN to filter a numeric range.
  • How BETWEEN works with date values.
  • How to negate a range check with NOT BETWEEN.

What is BETWEEN?

BETWEEN checks whether a value falls within a given range. It is inclusive, meaning the lower and upper bounds themselves are included in the match. A value exactly equal to either bound counts as a match.

Throughout this lesson we will use a students table with columns id, name, marks, grade, and join_date.

idnamemarksgradejoin_date
1Alex78A2024-01-10
2Sam65B2024-02-15
3Priya92A2024-01-22
4John55C2024-03-05
5Meera70B2024-02-01
6Arjun45D2024-03-18

Syntax

column_name BETWEEN lower_bound AND upper_bound
Note

BETWEEN is always written as BETWEEN low AND high, in that order. The lower bound comes first, the upper bound comes second, and both are included in the results.

Example: Marks Between 60 and 80

Suppose you want every student whose marks fall between 60 and 80, inclusive. Without BETWEEN, you would write it using AND:

SELECT name, marks
FROM students
WHERE marks >= 60 AND marks <= 80;

BETWEEN expresses exactly the same condition more clearly:

marks_between.sql
SELECT name, marks
FROM students
WHERE marks BETWEEN 60 AND 80;
Result
+-------+-------+
| name  | marks |
+-------+-------+
| Alex  | 78    |
| Sam   | 65    |
| Meera | 70    |
+-------+-------+

Priya (92) is excluded because 92 is above the upper bound, and John (55) and Arjun (45) are excluded because they fall below the lower bound. Notice that if a student had exactly 60 or exactly 80 marks, they would still be included, because BETWEEN is inclusive on both ends.

BETWEEN with Dates

BETWEEN is not limited to numbers — it works the same way on date and datetime values, since dates can be compared and ordered just like numbers.

between_dates.sql
SELECT name, join_date
FROM students
WHERE join_date BETWEEN '2024-01-01' AND '2024-02-15';
Result
+-------+------------+
| name  | join_date  |
+-------+------------+
| Alex  | 2024-01-10 |
| Priya | 2024-01-22 |
| Meera | 2024-02-01 |
| Sam   | 2024-02-15 |
+-------+------------+

This query finds every student who joined between January 1st and February 15th, 2024, inclusive. Sam is included because their join_date, 2024-02-15, matches the upper bound exactly.

Watch Out With DATETIME

If a column stores DATETIME values with a time component, BETWEEN '2024-02-15' AND '2024-02-15' will actually mean midnight to midnight, and may miss rows recorded later that same day. When you need a full day's range on a DATETIME column, it is safer to write BETWEEN '2024-02-15 00:00:00' AND '2024-02-15 23:59:59'.

NOT BETWEEN

Adding NOT before BETWEEN reverses the condition, matching every value outside the range instead of inside it.

SELECT name, marks
FROM students
WHERE marks NOT BETWEEN 60 AND 80;
Result
+-------+-------+
| name  | marks |
+-------+-------+
| Priya | 92    |
| John  | 55    |
| Arjun | 45    |
+-------+-------+

This returns every student whose marks are strictly below 60 or strictly above 80 — the exact opposite set of rows from the earlier example.

Common Mistakes

Avoid These Mistakes
  • Writing the bounds in the wrong order, like BETWEEN 80 AND 60 — MySQL expects the lower bound first and will return no rows if it is reversed.
  • Forgetting that BETWEEN is inclusive, and double-counting or excluding boundary values by mistake.
  • Assuming BETWEEN works on unrelated pairs of columns instead of a single column against two bounds.
  • Mixing date formats inconsistently, which can cause silent comparison errors.

Best Practices

  • Prefer BETWEEN over chained AND conditions when checking a single column against a range — it reads more clearly.
  • Always double check whether your bounds should be inclusive; if not, use plain > and < instead.
  • When working with DATETIME columns, be explicit about the time portion of your bounds.
  • Keep the lower bound first and the upper bound second, matching natural reading order.

Frequently Asked Questions

Is BETWEEN inclusive or exclusive?

BETWEEN is inclusive on both ends. A value equal to either the lower or upper bound is included in the results.

Can BETWEEN be used with text values?

Yes, BETWEEN can compare strings alphabetically, for example WHERE name BETWEEN 'A' AND 'M', but this is used far less often than numeric or date ranges.

What happens if the lower bound is greater than the upper bound?

MySQL will not automatically swap them. If you write BETWEEN 80 AND 60, no rows will match, because MySQL literally checks value >= 80 AND value <= 60, which is impossible.

Is BETWEEN faster than writing two AND conditions?

They perform identically — BETWEEN is simply shorthand that MySQL rewrites internally into the same two comparisons.

Key Takeaways

  • BETWEEN checks whether a value falls within a range, and it is inclusive on both ends.
  • The syntax is column BETWEEN lower_bound AND upper_bound, in that order.
  • BETWEEN works on numbers and on dates, since both can be ordered and compared.
  • NOT BETWEEN reverses the check, matching every value outside the given range.

Summary

BETWEEN is a clean, readable way to filter rows that fall within an inclusive range, whether that range is numeric or a span of dates. It saves you from writing two separate comparison conditions joined by AND.

In this lesson, you learned how BETWEEN works, saw it applied to both marks and join dates, and learned how NOT BETWEEN flips the condition. Next, you will learn the IN operator, which checks a value against a list of possibilities instead of a range.

Lesson 37 Completed
  • You understand that BETWEEN is inclusive on both bounds.
  • You can filter numeric ranges with BETWEEN.
  • You can filter date ranges with BETWEEN.
  • You know how to negate a range with NOT BETWEEN.
Next Lesson →

IN Operator