• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL BETWEEN

← Previous Next →

In MySQL, the BETWEEN operator is used in a WHERE clause to filter rows based on a range of values. It allows you to select records where a column’s value falls between two specified values, including the boundaries.

BETWEEN is versatile and can be used with numeric values, dates, and even text. It provides a clean and readable alternative to using multiple comparison operators like >= and <=.

Basic Syntax

The syntax for the BETWEEN operator is:

SELECT column1, column2
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

  • column_name is the column to check.

  • value1 is the lower bound and value2 is the upper bound.

  • Both value1 and value2 are inclusive, meaning they are included in the results.

Example:

SELECT name, age FROM students
WHERE age BETWEEN 18 AND 22;

  • Returns students whose age is 18, 19, 20, 21, or 22.

Using BETWEEN with Numbers

BETWEEN is commonly used with numeric values for range filtering:

SELECT name, score FROM students
WHERE score BETWEEN 80 AND 100;

  • Returns students whose scores are within 80 to 100.

Equivalent query using >= and <=:

SELECT name, score FROM students
WHERE score >= 80 AND score <= 100;

  • BETWEEN makes the query more readable and concise.

Using BETWEEN with Dates

BETWEEN is very useful when working with date ranges, such as filtering records by month, year, or a specific period:

SELECT name, join_date FROM students
WHERE join_date BETWEEN '2025-01-01' AND '2025-12-31';

  • Returns students who joined in the year 2025.

  • MySQL automatically understands date formats for comparisons.

Important Note

  • When using dates, ensure the format matches the column’s data type (DATE, DATETIME, etc.).

  • BETWEEN includes both start and end dates.

Using BETWEEN with Text

BETWEEN can also be used with string values, performing lexicographical comparison:

SELECT name FROM students
WHERE name BETWEEN 'A' AND 'M';

  • Returns names starting from A to M alphabetically.

  • Useful for alphabetical grouping of records.

Using NOT BETWEEN

To exclude a range, use NOT BETWEEN:

SELECT name, age FROM students
WHERE age NOT BETWEEN 18 AND 22;

  • Returns students younger than 18 or older than 22.

This is simpler than using OR with < and > operators.

BETWEEN vs AND

  • The AND in BETWEEN is part of the operator, not a logical AND.

  • Example:

WHERE age BETWEEN 18 AND 22

  • Is equivalent to:

WHERE age >= 18 AND age <= 22

  • Using BETWEEN improves readability, especially for longer queries with multiple ranges.

Combining BETWEEN with Other Conditions

BETWEEN can be combined with other operators and conditions:

SELECT name, age, city FROM students
WHERE age BETWEEN 18 AND 22
  AND city IN ('Mumbai', 'Pune');

  • Filters students whose age is 18–22 and who live in Mumbai or Pune.

You can also combine with OR:

SELECT name, age FROM students
WHERE age BETWEEN 18 AND 22 OR score BETWEEN 80 AND 100;

  • Returns students in either age range 18–22 or score range 80–100.

BETWEEN with GROUP BY and ORDER BY

BETWEEN can be used in aggregated queries:

SELECT city, COUNT(*) AS total_students
FROM students
WHERE age BETWEEN 18 AND 22
GROUP BY city;

  • Counts students in each city within the age range 18–22.

Use ORDER BY to sort results:

SELECT name, age FROM students
WHERE age BETWEEN 18 AND 22
ORDER BY age ASC;

  • Returns students sorted by age within the range.

Practical Scenarios

  1. Filter students by age range:

SELECT name, age FROM students
WHERE age BETWEEN 18 AND 22;

  • Quickly selects students eligible for a program based on age criteria.

  1. Filter students by joining dates:

SELECT name, join_date FROM students
WHERE join_date BETWEEN '2025-01-01' AND '2025-06-30';

  • Selects students who joined in the first half of 2025.

  1. Filter numeric ranges for scores:

SELECT name, score FROM students
WHERE score BETWEEN 50 AND 75;

  • Finds students who need improvement based on their scores.

  1. Alphabetical range using strings:

SELECT name FROM students
WHERE name BETWEEN 'A' AND 'M';

  • Groups students alphabetically for reporting.

  1. Exclude ranges:

SELECT name, age FROM students
WHERE age NOT BETWEEN 18 AND 22;

  • Useful for filtering out records outside the desired range.

Important Points

  1. BETWEEN is inclusive: both lower and upper bounds are included.

  2. Works with numbers, dates, and strings.

  3. Can be combined with IN, NOT IN, AND, OR for complex filters.

  4. Improves query readability compared to using multiple comparison operators.

  5. Always ensure data types match the values you are using with BETWEEN.

  6. Using NOT BETWEEN excludes the specified range efficiently.

Real-Life Example

Suppose the students table contains:

id name age score join_date
1 Aditi 19 85 2025-01-15
2 Meera 20 90 2025-03-10
3 Pooja 18 88 2025-05-25
4 Riya 21 95 2024-12-30
5 Ananya 22 78 2025-02-20

Example 1 – Students aged 18–22:

SELECT name FROM students
WHERE age BETWEEN 18 AND 22;

  • Returns: Aditi, Meera, Pooja, Riya, Ananya

Example 2 – Students joined in first half of 2025:

SELECT name FROM students
WHERE join_date BETWEEN '2025-01-01' AND '2025-06-30';

  • Returns: Aditi, Meera, Pooja, Ananya

Example 3 – Students with scores outside 80–90:

SELECT name, score FROM students
WHERE score NOT BETWEEN 80 AND 90;

  • Returns: Riya

Example 4 – Alphabetical grouping:

SELECT name FROM students
WHERE name BETWEEN 'A' AND 'M';

  • Returns: Aditi, Ananya, Meera, Pooja

Summary of the Tutorial

  • The BETWEEN operator allows filtering rows based on a range of values.

  • It works with numbers, dates, and strings, and is inclusive of boundaries.

  • Use NOT BETWEEN to exclude a range.

  • BETWEEN simplifies queries compared to multiple >= and <= conditions.

  • Combine BETWEEN with IN, AND, OR, GROUP BY, ORDER BY for advanced queries.

Mastering BETWEEN helps in range-based filtering, reporting, and dynamic data selection, making your SQL queries more efficient and readable.

Practice Questions

  1. Retrieve all students whose age is between 18 and 22.

  2. Retrieve students whose score is between 80 and 90.

  3. Retrieve students who joined between '2025-01-01' and '2025-06-30'.

  4. Retrieve students whose age is not between 18 and 22.

  5. Retrieve students whose score is not between 70 and 85.

  6. Retrieve students whose names are alphabetically between 'A' and 'M'.

  7. Retrieve students whose age is between 18 and 22 and city is 'Mumbai'.

  8. Retrieve students whose score is between 50 and 100 or age is between 18 and 20.

  9. Count the number of students whose age is between 19 and 21.

  10. Retrieve students whose join_date is between '2024-12-01' and '2024-12-31'.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top