How to Use the MySQL BETWEEN Operator (With Examples for Numbers, Dates, and Strings)

1. Introduction

One of the key elements for running queries efficiently in MySQL databases is the BETWEEN operator. This operator is extremely useful when checking whether data falls within a specific range. It can be applied to numbers, dates, and strings, making it a powerful tool for filtering and retrieving data efficiently. In this article, we will explain how to use the MySQL BETWEEN operator, provide practical examples, and highlight important points to keep in mind.

2. Basic Syntax of the BETWEEN Operator

What is the BETWEEN Operator?

The BETWEEN operator is used in the WHERE clause to check if a column’s value falls within a specified range. Its basic syntax is as follows:

SELECT column_name
FROM table_name
WHERE column_name BETWEEN start_value AND end_value;

For example, if a table stores employees’ ages, you can use this operator to extract only those within a specific age range.

Negative Form: NOT BETWEEN

If you need to search for values outside the specified range, you can use the negative form NOT BETWEEN:

SELECT column_name
FROM table_name
WHERE column_name NOT BETWEEN start_value AND end_value;

3. Numeric Range Filtering

Using BETWEEN with Numbers

The BETWEEN operator is very handy when filtering numeric ranges. For example, if you want to extract employees with salaries between 50,000 and 100,000, you can write the query as follows:

SELECT employee_id, name, salary
FROM employees
WHERE salary BETWEEN 50000 AND 100000;

Sample Data:

Result:

In this query, only employees whose salaries fall between 50,000 and 100,000 are selected.

Comparison Operators vs. BETWEEN

The same condition can also be expressed with comparison operators, like this:

SELECT employee_id, name, salary
FROM employees
WHERE salary >= 50000 AND salary <= 100000;

Using BETWEEN makes the query more concise and easier to read. This is especially useful when dealing with multiple range conditions.

4. Date Range Filtering

Using BETWEEN with Dates

The BETWEEN operator can also be applied to date ranges. For example, to retrieve orders placed between January 1, 2024 and December 31, 2024, you can write:

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

Sample Data:

Result:

As shown above, the BETWEEN operator extracts data within the specified date range.

Handling Time Data

If the date column includes time values, you need to be careful. For example, if the order_date column is of type DATETIME, using BETWEEN will include values from midnight (00:00:00) of the start date up to midnight of the next day after the end date. To make sure you capture the full day, specify the time component explicitly:

WHERE order_date BETWEEN '2024-01-01 00:00:00' AND '2024-12-31 23:59:59';

5. String Range Filtering

Using BETWEEN with Strings

The BETWEEN operator can also be applied to string data. For example, if you want to search for products whose names fall alphabetically between ‘A’ and ‘M’, the query would look like this:

SELECT product_id, product_name
FROM products
WHERE product_name BETWEEN 'A' AND 'M';

Sample Data:

Result:

Alphabetical Order Considerations

When filtering strings with BETWEEN, keep in mind that the evaluation is based on alphabetical order. Also, depending on the database collation settings, case sensitivity may apply. For instance, 'a' and 'A' may be treated as different values. Always confirm the collation settings when working with string ranges.

6. Important Notes on the BETWEEN Operator

Order of Range Values

Be cautious about the order of the range values. If the starting value is greater than the ending value, the query may return unintended results:

SELECT * 
FROM table_name 
WHERE column_name BETWEEN 100 AND 50;  -- Unintended result

Also, remember that BETWEEN is inclusive of both ends of the range, so choose your values carefully.

Indexes and Query Performance

The BETWEEN operator generally performs the same as comparison operators. However, to optimize performance, proper indexing is essential. For example, if you apply an index to a date column, queries using BETWEEN on that column will execute more efficiently.

7. Practical Queries and Use Cases

Using BETWEEN on Multiple Columns

You can also combine BETWEEN conditions on multiple columns. For example, if you want to filter products based on both price and stock range, you can write:

SELECT product_name, price, stock
FROM products
WHERE price BETWEEN 1000 AND 5000
  AND stock BETWEEN 50 AND 200;

This query retrieves products priced between 1000 and 5000, with stock levels between 50 and 200.

Practical Use of NOT BETWEEN

By using the negative form NOT BETWEEN, you can easily extract data outside a specific range. For example, to find employees whose salaries are less than 50,000 or greater than 100,000, you can write:

SELECT employee_id, name, salary
FROM employees
WHERE salary NOT BETWEEN 50000 AND 100000;

Result:

This query retrieves employees whose salaries do not fall between 50,000 and 100,000. Using NOT BETWEEN makes it straightforward to apply the opposite condition.

8. Visual Examples of Queries

To make query results more intuitive, visual diagrams can be helpful. For example, you can illustrate the effect of BETWEEN as follows:

Price Range: [----- 1000 ---- 5000 -----]
Product A Price: 3000 (Inside Range)
Product B Price: 6000 (Outside Range)

Such illustrations make it easier to understand whether a value falls within the range specified in the query.

9. Summary

The BETWEEN operator is a powerful tool in MySQL for range-based searches. It can be applied to numeric, date, and string data, allowing you to write queries that are both concise and efficient. However, it’s important to understand its characteristics—such as inclusivity of boundary values and the importance of proper indexing—to avoid unexpected results. By applying this knowledge, you can optimize query performance and extract only the data you need more effectively.

10. References

For further details and advanced use cases, you can refer to the official MySQL documentation or specialized database books. Additionally, experimenting with queries yourself is one of the best ways to deepen your understanding of how the BETWEEN operator works.