Filtering Ranges Using BETWEEN in MySQL – Detailed Notes

Course: MySQL Training

Topic: Filtering Ranges Using BETWEEN

Course Link: MySQL Training Course

Demo Link: Register for a Free Course Demo

1. Introduction to the BETWEEN Operator in MySQL

The BETWEEN operator in MySQL is used to filter records whose values fall within a specified range. It is especially useful when working with numerical values, dates, times, and other comparable data types.

For example, a company may want to retrieve employees whose salaries are between ₹25,000 and ₹50,000, a school may want to find students whose marks fall between 60 and 90, or an online store may want to display products priced between ₹500 and ₹2,000.

Instead of writing multiple comparison conditions using greater-than-or-equal-to and less-than-or-equal-to operators, you can use BETWEEN to write a shorter and more readable query.

An important feature of BETWEEN is that it is inclusive. This means that both the lower boundary and the upper boundary are included in the result when the comparison is true.

Example

SELECT *
FROM employees
WHERE salary BETWEEN 25000 AND 50000;

This query retrieves employees whose salaries are at least 25,000 and at most 50,000, including employees earning exactly 25,000 or 50,000.

2. Learning Objectives

After studying this topic, you will be able to:

• Understand the purpose and syntax of the BETWEEN operator.
• Filter numerical values within a specified range.
• Retrieve records using date and time ranges.
• Understand the inclusive nature of BETWEEN.
• Use NOT BETWEEN to exclude a range of values.
• Combine BETWEEN with AND, OR, and other WHERE conditions.
• Apply BETWEEN to practical database scenarios.
• Identify common mistakes involving range boundaries, NULL values, and dates.

3. What Is the BETWEEN Operator?

BETWEEN is a comparison operator used inside a WHERE clause to test whether an expression falls within a lower and upper boundary.

The operator is commonly used with numeric, date, and time values. It can also be used with strings, although string comparisons depend on the column's data type, character set, and collation.

General Syntax

SELECT column1, column2
FROM table_name
WHERE column_name BETWEEN lower_value AND upper_value;

Syntax Explanation

Important: The AND inside BETWEEN separates the two range boundaries. It is not the same as adding a separate AND condition to the WHERE clause, although both can appear in the same query.

4. Understanding Inclusive Range Boundaries

MySQL includes both endpoints when using BETWEEN. The expression is equivalent to checking whether the value is greater than or equal to the lower boundary and less than or equal to the upper boundary.

Equivalent Conditions

WHERE salary BETWEEN 25000 AND 50000;

The above condition is equivalent to:

WHERE salary >= 25000
  AND salary <= 50000;

Example Results

Remember that BETWEEN does not exclude boundary values. If a requirement says that the value must be strictly greater than the lower boundary and strictly less than the upper boundary, use the appropriate comparison operators instead.

5. Creating a Sample Database and Table

To understand BETWEEN practically, create a sample database and a table containing employee details.

Step 1: Create a Database

CREATE DATABASE company_db;

Step 2: Select the Database

USE company_db;

Step 3: Create the Employees Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department VARCHAR(50),
    age INT,
    salary DECIMAL(10,2),
    joining_date DATE
);

Step 4: Insert Sample Records

INSERT INTO employees
(employee_id, employee_name, department, age, salary, joining_date)
VALUES
(1, 'Aarav', 'IT', 24, 28000, '2023-01-15'),
(2, 'Priya', 'HR', 29, 42000, '2022-06-10'),
(3, 'Rahul', 'Finance', 35, 55000, '2021-03-20'),
(4, 'Sneha', 'IT', 27, 35000, '2024-02-05'),
(5, 'Karan', 'Sales', 41, 65000, '2020-11-12'),
(6, 'Meera', 'HR', 32, 48000, '2023-08-18'),
(7, 'Ishaan', 'IT', 30, 50000, '2022-12-01'),
(8, 'Ananya', 'Finance', 26, 31000, '2024-05-22'),
(9, 'Vikram', 'Sales', 38, 45000, '2021-09-14'),
(10, 'Riya', 'Marketing', 23, 25000, '2025-01-08');

Step 5: View All Records

SELECT * FROM employees;

This table will be used for the examples throughout these notes.

6. Filtering Numeric Ranges Using BETWEEN

The most common use of BETWEEN is to filter numerical columns such as age, salary, marks, product price, quantity, and discount percentage.

Example 1: Filter Employees by Salary

SELECT employee_name, salary
FROM employees
WHERE salary BETWEEN 30000 AND 50000;

Explanation: This query returns employees whose salaries are from 30,000 through 50,000, including both endpoints.

Based on the sample data, the matching employees are Priya, Sneha, Meera, Ishaan, Ananya, and Vikram, along with their respective salaries when they fall within the specified range.

Example 2: Filter Employees by Age

SELECT employee_name, age
FROM employees
WHERE age BETWEEN 25 AND 35;

This query returns employees aged 25 through 35, inclusive.

Example 3: Find Employees Earning Between ₹40,000 and ₹60,000

SELECT employee_id, employee_name, department, salary
FROM employees
WHERE salary BETWEEN 40000 AND 60000;

This query is useful when an organization needs to identify employees in a particular salary band for budgeting, compensation analysis, or reporting.

Example 4: Filter Product Prices

SELECT product_name, price
FROM products
WHERE price BETWEEN 500 AND 2000;

This query assumes that a products table exists with product_name and price columns. It retrieves products priced between 500 and 2,000, including the endpoints.

Example 5: Filter Exam Marks

SELECT student_name, marks
FROM students
WHERE marks BETWEEN 60 AND 90;

This query retrieves students with marks from 60 to 90, including students who scored exactly 60 or 90.

7. Filtering Date Ranges Using BETWEEN

BETWEEN can filter date values when the column uses a suitable date or datetime data type. Date range filtering is common in attendance systems, order reports, employee records, bookings, and financial transactions.

Example 1: Employees Who Joined During 2023

SELECT employee_name, joining_date
FROM employees
WHERE joining_date BETWEEN '2023-01-01' AND '2023-12-31';

This query retrieves employees whose joining dates fall within the calendar year 2023.

Example 2: Retrieve Orders Within a Month

SELECT order_id, customer_id, order_date
FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31';

This example assumes an orders table with order_id, customer_id, and order_date columns. It works as expected when order_date is a DATE column.

Important: DATE Versus DATETIME

A DATETIME column stores both a date and a time. If a column contains timestamps and you write:

WHERE order_datetime
BETWEEN '2026-01-01' AND '2026-01-31';

MySQL interprets the upper boundary as midnight at the start of January 31. Records later on January 31 may therefore be excluded.

For a full month when the column is DATETIME or TIMESTAMP, a safer pattern is a half-open range: include the start and exclude the start of the following period.

SELECT order_id, customer_id, order_datetime
FROM orders
WHERE order_datetime >= '2026-01-01'
  AND order_datetime < '2026-02-01';

This pattern includes all times on January 31 without needing to guess the last possible fractional second. Use date boundaries appropriate to the report you are building.

8. Filtering Time Ranges

When a table contains a TIME column, BETWEEN can filter values within a time interval.

Example: Find Appointments During Morning Hours

SELECT appointment_id, appointment_time
FROM appointments
WHERE appointment_time BETWEEN '09:00:00' AND '12:00:00';

This query returns appointments scheduled from 9:00 AM through 12:00 PM, including appointments at exactly those times.

For DATETIME columns, use full date-and-time boundaries or the half-open range pattern when filtering a particular day or period.

9. Using NOT BETWEEN

The NOT BETWEEN operator retrieves values that fall outside a specified range. It is useful when you want to exclude a salary band, age group, price range, or date interval.

Syntax

SELECT column_name
FROM table_name
WHERE column_name NOT BETWEEN lower_value AND upper_value;

Example 1: Employees Outside a Salary Range

SELECT employee_name, salary
FROM employees
WHERE salary NOT BETWEEN 30000 AND 50000;

This query retrieves employees earning less than 30,000 or more than 50,000. Salaries equal to 30,000 or 50,000 are excluded because those values are inside the BETWEEN range.

Example 2: Employees Outside an Age Group

SELECT employee_name, age
FROM employees
WHERE age NOT BETWEEN 25 AND 35;

This query returns employees younger than 25 or older than 35.

Equivalent Conditions

WHERE salary NOT BETWEEN 30000 AND 50000;

This is equivalent to:

WHERE salary < 30000
   OR salary > 50000;

Note: SQL uses three-valued logic. If salary is NULL, neither BETWEEN nor NOT BETWEEN evaluates to TRUE for that row, so the row is not returned by the WHERE clause.

10. Combining BETWEEN with AND

You can combine a BETWEEN condition with another condition using AND. In this case, both the range condition and the additional condition must be true for a record to be returned.

Example 1: Salary Range and Department

SELECT employee_name, department, salary
FROM employees
WHERE salary BETWEEN 30000 AND 50000
  AND department = 'IT';

This query returns employees in the IT department whose salaries fall between 30,000 and 50,000, inclusive.

Example 2: Age Range and Minimum Salary

SELECT employee_name, age, salary
FROM employees
WHERE age BETWEEN 25 AND 35
  AND salary >= 35000;

This query returns employees who meet both conditions: their age is within the specified range and their salary is at least 35,000.

Example 3: Date Range and Order Status

SELECT order_id, order_date, status
FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31'
  AND status = 'Completed';

This query assumes order_date is a DATE column. It retrieves completed orders placed during January 2026.

11. Combining BETWEEN with OR

The OR operator allows a record to match at least one of the specified conditions. Parentheses are helpful when combining BETWEEN with other logical operators because they make the intended logic explicit.

Example: Employees in a Salary Band or a Specific Department

SELECT employee_name, department, salary
FROM employees
WHERE salary BETWEEN 30000 AND 40000
   OR department = 'HR';

This query returns employees whose salary is in the specified range or whose department is HR.

Example: Two Separate Salary Bands

SELECT employee_name, salary
FROM employees
WHERE salary BETWEEN 25000 AND 30000
   OR salary BETWEEN 45000 AND 55000;

This query retrieves employees whose salaries fall into either of the two specified bands.

Using Parentheses for Clear Logic

SELECT employee_name, department, salary
FROM employees
WHERE department = 'IT'
  AND (
      salary BETWEEN 25000 AND 35000
      OR salary BETWEEN 45000 AND 55000
  );

This query restricts results to the IT department and then checks whether the salary falls within either band.

12. Using BETWEEN with ORDER BY

BETWEEN filters the records, while ORDER BY controls how the matching records are sorted. Both clauses can be used in the same SELECT statement.

Example 1: Sort Employees by Salary

SELECT employee_name, salary
FROM employees
WHERE salary BETWEEN 25000 AND 50000
ORDER BY salary ASC;

The query returns matching employees from the lowest salary to the highest salary.

Example 2: Sort Joining Dates from Newest to Oldest

SELECT employee_name, joining_date
FROM employees
WHERE joining_date BETWEEN '2022-01-01' AND '2024-12-31'
ORDER BY joining_date DESC;

This query retrieves employees who joined during the specified date range and sorts their joining dates in descending order.

13. Using BETWEEN with Aggregate Functions

BETWEEN can be used to filter records before applying aggregate functions such as COUNT(), SUM(), AVG(), MIN(), and MAX().

Example 1: Count Employees in a Salary Range

SELECT COUNT(*) AS employee_count
FROM employees
WHERE salary BETWEEN 30000 AND 50000;

This query counts employees whose salaries fall within the specified range.

Example 2: Calculate Average Salary in a Range

SELECT AVG(salary) AS average_salary
FROM employees
WHERE salary BETWEEN 30000 AND 50000;

This query calculates the average salary of employees who meet the range condition. AVG() ignores NULL salary values.

Example 3: Calculate Total Salary for a Salary Band

SELECT SUM(salary) AS total_salary
FROM employees
WHERE salary BETWEEN 25000 AND 40000;

This query calculates the sum of salaries for employees whose salaries fall within the specified band.

14. Using BETWEEN with GROUP BY

GROUP BY groups the filtered records by one or more columns. It is useful for comparing departments, categories, or other groups within a selected range.

Example: Count Employees by Department

SELECT department, COUNT(*) AS employee_count
FROM employees
WHERE salary BETWEEN 30000 AND 50000
GROUP BY department
ORDER BY employee_count DESC;

This query first filters employees by salary, then counts the matching employees in each department, and finally sorts departments by their counts.

Example: Average Salary by Department

SELECT department, AVG(salary) AS average_salary
FROM employees
WHERE salary BETWEEN 25000 AND 60000
GROUP BY department
ORDER BY average_salary DESC;

This query compares department-level average salaries among employees whose salaries fall within the selected range.

15. Using BETWEEN with UPDATE

BETWEEN can be used in an UPDATE statement to modify only records whose values fall within a specified range. Because UPDATE changes stored data, always verify the target records before running the statement.

Example: Update a Bonus for Employees in a Salary Band

Suppose an employees table has a bonus column. The following example increases the bonus for employees whose salaries fall between 30,000 and 40,000.

UPDATE employees
SET bonus = bonus + 1000
WHERE salary BETWEEN 30000 AND 40000;

This query assumes the bonus column exists and contains values that can be increased. If bonus can be NULL, the expression bonus + 1000 will remain NULL for those rows unless you handle NULL explicitly.

Safer Workflow Before Updating

SELECT employee_id, employee_name, salary
FROM employees
WHERE salary BETWEEN 30000 AND 40000;

Review the selected records first. If the results are correct, perform the UPDATE. In important production systems, consider using a transaction when supported by the table's storage engine and your workflow.

16. Using BETWEEN with DELETE

BETWEEN can also filter rows for a DELETE statement. Deleting records is a destructive operation, so confirm the range and inspect the affected rows before proceeding.

Example

DELETE FROM temporary_orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-01-31';

This example deletes rows from a temporary_orders table when order_date is a DATE column and falls within January 2025.

Important Safety Practices

• Run a SELECT statement with the same WHERE condition before deleting records.
• Confirm that the lower and upper boundaries are correct.
• Check whether the column is DATE or DATETIME.
• Use transactions where appropriate and supported.
• Never run a DELETE statement without checking its intended scope.

17. BETWEEN with NULL Values

NULL represents missing or unknown data in SQL. A comparison involving NULL generally produces UNKNOWN rather than TRUE or FALSE.

Example

SELECT employee_name, salary
FROM employees
WHERE salary BETWEEN 30000 AND 50000;

If an employee's salary is NULL, that employee will not appear in the result. The condition cannot establish that the unknown salary falls within the range.

Finding NULL Values

SELECT employee_name, salary
FROM employees
WHERE salary IS NULL;

Use IS NULL to find missing values and IS NOT NULL to find values that are present. Do not use BETWEEN to test whether a value is NULL.

18. Understanding Reversed Boundaries

The lower boundary should normally be less than or equal to the upper boundary. If the boundaries are reversed, BETWEEN does not automatically swap them.

Example

SELECT employee_name, salary
FROM employees
WHERE salary BETWEEN 50000 AND 30000;

For ordinary numeric salary values, this condition will not match values because a value cannot be both greater than or equal to 50,000 and less than or equal to 30,000.

Always confirm that the lower boundary comes first and the upper boundary comes second.

19. BETWEEN with String Values

BETWEEN can compare strings, but string range behavior depends on the column's collation and comparison rules. It is not always suitable for expressing business ranges such as alphabetical categories or human-friendly name intervals.

Example

SELECT employee_name
FROM employees
WHERE employee_name BETWEEN 'A' AND 'M';

This query compares employee_name values lexicographically according to the applicable collation. The result should not be interpreted as a simple, universal rule that every name beginning with letters A through M will always be included. The full string values and collation rules matter.

For clearer filtering, use a more explicit condition when the actual requirement is to match particular prefixes or categories.

20. BETWEEN vs. Comparison Operators

BETWEEN is a convenient way to express an inclusive range. Comparison operators are useful when the boundaries need to be exclusive or the condition is more complex.

21. BETWEEN vs. IN

BETWEEN and IN both filter values, but they serve different purposes.

BETWEEN Example

SELECT employee_name, age
FROM employees
WHERE age BETWEEN 25 AND 35;

IN Example

SELECT employee_name, age
FROM employees
WHERE age IN (25, 30, 35);

The first query can return any age within the range. The second query returns only records with ages 25, 30, or 35.

22. Common Mistakes When Using BETWEEN

Mistake 1: Forgetting That Boundaries Are Inclusive

SELECT *
FROM employees
WHERE salary BETWEEN 30000 AND 50000;

Problem: A developer expects employees earning exactly 30,000 or 50,000 to be excluded.

Solution: Use salary > 30000 AND salary < 50000 if both boundaries must be excluded.

Mistake 2: Using BETWEEN for a Full DATETIME Month

WHERE order_datetime
BETWEEN '2026-01-01' AND '2026-01-31';

Problem: The upper endpoint represents midnight at the start of January 31, not the end of that day.

Solution: Use order_datetime >= '2026-01-01' AND order_datetime < '2026-02-01'.

Mistake 3: Reversing the Range

WHERE age BETWEEN 40 AND 20;

Problem: The lower and upper boundaries are reversed.

Solution: Write age BETWEEN 20 AND 40.

Mistake 4: Assuming NULL Values Will Be Included

Problem: Records with NULL values are not selected by a normal BETWEEN condition.

Solution: Add an explicit NULL condition if the business requirement calls for those records.

SELECT employee_name, salary
FROM employees
WHERE salary BETWEEN 30000 AND 50000
   OR salary IS NULL;

Mistake 5: Using the Wrong Data Type

Problem: Comparing dates or numbers stored in unsuitable text columns can lead to confusing behavior and implicit conversions.

Solution: Prefer appropriate types such as INT, DECIMAL, DATE, DATETIME, and TIME for the values being stored and compared.

Mistake 6: Ignoring Logical Operator Precedence

Problem: Combining AND and OR without clear grouping can produce unintended results.

Solution: Use parentheses to communicate the intended logic.

23. Performance and Best Practices

• Choose appropriate data types: Store numbers as numeric types and dates as DATE, DATETIME, or TIMESTAMP where appropriate.
• Use clear boundaries: Document whether range endpoints are inclusive or exclusive.
• Be careful with datetime ranges: For complete calendar periods, consider a start-inclusive, end-exclusive condition.
• Keep indexed columns searchable: A direct range condition such as WHERE salary BETWEEN 30000 AND 50000 can allow MySQL to use an appropriate index, depending on the query and data distribution.
• Check query plans: Use EXPLAIN to investigate how MySQL executes a query.
• Avoid unnecessary functions on filtered columns: Applying functions to indexed columns can prevent efficient index range access in some cases.
• Validate input: Check that the lower boundary is not greater than the upper boundary when ranges come from a form or application.
• Use meaningful aliases: Give aggregate results readable names such as employee_count or average_salary.
• Test edge cases: Verify values exactly at both boundaries, values just outside them, and NULL values.
• Protect destructive queries: Preview the rows before using BETWEEN in UPDATE or DELETE.

Example: Inspect a Query Plan

EXPLAIN
SELECT employee_id, employee_name, salary
FROM employees
WHERE salary BETWEEN 30000 AND 50000;

EXPLAIN displays information about the execution plan. Whether MySQL chooses an index depends on the available indexes, table size, statistics, and the estimated cost of different plans.

24. Practical Project: Employee Salary Range Report

Project Objective: Create a report that retrieves employees within a salary range, filters by department, sorts the results, and calculates summary statistics.

Task 1: Retrieve Employees Within a Salary Range

SELECT employee_id, employee_name, department, salary
FROM employees
WHERE salary BETWEEN 30000 AND 50000;

Task 2: Filter IT Employees Within the Same Range

SELECT employee_name, department, salary
FROM employees
WHERE salary BETWEEN 30000 AND 50000
  AND department = 'IT';

Task 3: Sort the Results by Salary

SELECT employee_name, salary
FROM employees
WHERE salary BETWEEN 30000 AND 50000
ORDER BY salary DESC;

Task 4: Count Employees in the Range

SELECT COUNT(*) AS employee_count
FROM employees
WHERE salary BETWEEN 30000 AND 50000;

Task 5: Calculate the Average Salary

SELECT AVG(salary) AS average_salary
FROM employees
WHERE salary BETWEEN 30000 AND 50000;

Task 6: Find Employees Outside the Range

SELECT employee_name, salary
FROM employees
WHERE salary NOT BETWEEN 30000 AND 50000;

Expected Learning Outcome

After completing this project, you should be able to retrieve records from a range, apply additional filters, sort results, calculate summary statistics, and identify records outside a range.

25. Interview Questions and Answers

Q1. What is BETWEEN in MySQL?

BETWEEN is a comparison operator used to check whether a value falls within a specified lower and upper boundary.

Q2. Is BETWEEN inclusive in MySQL?

Yes. BETWEEN includes both the lower and upper boundaries.

Q3. What is the syntax of BETWEEN?

SELECT column_name
FROM table_name
WHERE column_name BETWEEN lower_value AND upper_value;

Q4. What is the difference between BETWEEN and NOT BETWEEN?

BETWEEN selects values inside the inclusive range. NOT BETWEEN selects values outside that range. NULL values do not satisfy either condition as TRUE.

Q5. Can BETWEEN be used with dates?

Yes. It can filter DATE values and can also compare datetime values. For DATETIME columns, ensure that the upper boundary includes the intended time period.

Q6. Can BETWEEN be used with numbers?

Yes. It is commonly used with integers, decimal amounts, prices, ages, marks, and other numeric values.

Q7. What happens when the boundaries are reversed?

MySQL does not automatically swap the boundaries. For ordinary values, a range such as BETWEEN 50 AND 20 matches no value because the lower boundary exceeds the upper boundary.

Q8. How can you select employees earning between 20,000 and 40,000?

SELECT *
FROM employees
WHERE salary BETWEEN 20000 AND 40000;

Q9. How do you find records outside a range?

SELECT *
FROM employees
WHERE salary NOT BETWEEN 20000 AND 40000;

Q10. What happens when the tested column contains NULL?

The condition evaluates to UNKNOWN, so the row is not returned by the WHERE clause unless another condition explicitly matches it.

Q11. Can BETWEEN be combined with AND?

Yes. BETWEEN uses AND between its two boundaries, and a separate AND can add another condition to the WHERE clause.

Q12. What is the difference between BETWEEN and IN?

BETWEEN checks a continuous inclusive range. IN checks membership in a specified list of values.

Q13. Can BETWEEN be used with UPDATE?

Yes. It can restrict which rows an UPDATE statement modifies. Always preview the matching rows first.

Q14. Can BETWEEN be used with DELETE?

Yes. It can select rows for deletion, but the condition must be checked carefully before running the statement.

Q15. How can you include the whole month for a DATETIME column?

Use a start-inclusive and end-exclusive range, such as WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01'.

26. Practice Exercises

Use the sample employees table to complete the following exercises.

1. Display employees whose salaries are between 25,000 and 45,000.
2. Retrieve employees whose ages fall between 25 and 35.
3. Find employees whose salaries are not between 30,000 and 50,000.
4. Display employees who joined between January 1, 2022, and December 31, 2023.
5. Retrieve IT employees whose salaries are between 25,000 and 40,000.
6. Count employees whose salaries fall between 30,000 and 50,000.
7. Calculate the average salary of employees whose ages are between 25 and 35.
8. Display employees in either of two salary bands using BETWEEN and OR.
9. Sort employees in a salary range from highest to lowest salary.
10. Explain why a DATETIME query ending at midnight on the last date may omit records later that day.

27. Practice Exercise Solutions

Solution 1: Salary Between 25,000 and 45,000

SELECT employee_name, salary
FROM employees
WHERE salary BETWEEN 25000 AND 45000;

Solution 2: Ages Between 25 and 35

SELECT employee_name, age
FROM employees
WHERE age BETWEEN 25 AND 35;

Solution 3: Salaries Outside the Range

SELECT employee_name, salary
FROM employees
WHERE salary NOT BETWEEN 30000 AND 50000;

Solution 4: Joining Dates During 2022 and 2023

SELECT employee_name, joining_date
FROM employees
WHERE joining_date BETWEEN '2022-01-01' AND '2023-12-31';

Solution 5: IT Employees Within a Salary Range

SELECT employee_name, department, salary
FROM employees
WHERE salary BETWEEN 25000 AND 40000
  AND department = 'IT';

Solution 6: Count Employees Within a Salary Range

SELECT COUNT(*) AS employee_count
FROM employees
WHERE salary BETWEEN 30000 AND 50000;

Solution 7: Average Salary for Ages 25 to 35

SELECT AVG(salary) AS average_salary
FROM employees
WHERE age BETWEEN 25 AND 35;

Solution 8: Two Salary Bands

SELECT employee_name, salary
FROM employees
WHERE salary BETWEEN 25000 AND 30000
   OR salary BETWEEN 45000 AND 55000;

Solution 9: Sort Salary in Descending Order

SELECT employee_name, salary
FROM employees
WHERE salary BETWEEN 25000 AND 50000
ORDER BY salary DESC;

Solution 10: DATETIME Boundary Explanation

A date literal used as the upper boundary of a DATETIME comparison represents midnight at the start of that date. To include every time on the final day, use the beginning of the next day as an exclusive upper boundary.

28. Quick Revision Checklist

• BETWEEN filters values within a specified range.
• The lower and upper boundaries are both included.
• Use the format column BETWEEN lower_value AND upper_value.
• Use NOT BETWEEN to find values outside a range.
• BETWEEN works with numeric and date/time values and can compare strings according to their collation rules.
• NULL values do not satisfy BETWEEN or NOT BETWEEN as TRUE.
• Use parentheses when combining range conditions with AND and OR.
• For full DATETIME periods, consider a start-inclusive and end-exclusive range.
• Use SELECT to verify matching rows before UPDATE or DELETE.
• Use EXPLAIN to inspect query execution plans when performance matters.

29. Official Documentation and Further Learning

For more information about comparison operators and range conditions, refer to the official MySQL documentation.

• MySQL Comparison Functions and Operators – Reference for BETWEEN, NOT BETWEEN, and other comparison operators.
• MySQL Date and Time Types – Learn about DATE, DATETIME, TIMESTAMP, and TIME.
• MySQL EXPLAIN Statement – Understand how to inspect query execution plans.

30. Conclusion

The BETWEEN operator is an essential MySQL feature for filtering records within a range. It makes queries more readable when working with salary bands, ages, prices, quantities, dates, and times. Since both endpoints are included, it is important to choose the correct boundaries for each requirement.

By combining BETWEEN with WHERE, NOT BETWEEN, AND, OR, ORDER BY, GROUP BY, and aggregate functions, you can create useful reports and data filters. Pay special attention to NULL values and DATETIME boundaries, and always inspect the target rows before changing or deleting data.

To strengthen your SQL skills, practise these examples and explore the MySQL Training Course. You can also register for a course demo to learn more about the training.