MySQL BETWEEN Operator Explained (With Practical Examples)

3. Specifying a Numeric Range

How to Use BETWEEN for Numeric Ranges

The BETWEEN operator is very convenient for specifying numeric ranges. For example, if you want to extract employees whose salary is between 50,000 and 100,000, you can write the query like this:

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

Sample Data：

Execution Result：

In the query above, only employees whose salary falls within the range of 50,000 to 100,000 are selected.

Difference from Comparison Operators

If you write the same condition using comparison operators, it would look like this:

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

Using BETWEEN makes the query more concise and improves readability. This is especially helpful when you have multiple range conditions, because BETWEEN keeps the query easier to read.

4. Specifying a Date Range

How to Use BETWEEN with Date Data

BETWEEN can also be used to specify a range for date data. For example, to extract orders from an orders table between January 1, 2024 and December 31, 2024, you can write the query like this:

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

Sample Data：

Execution Result：

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

Handling Time Values

If your date data includes time values, you need to be careful. For example, if the order_date column is of type DATETIME and includes time, using BETWEEN will target the range from exactly midnight of the start date to exactly midnight of the end date unless you specify the time properly.

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

5. Specifying a String Range

How to Use BETWEEN with String Data

The BETWEEN operator can also be applied to string data. For example, if you want to search for products whose names fall within the range from ‘A’ to ‘M’, you can write the query like this:

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

Sample Data：

Execution Result：

Notes on Alphabetical Order

When specifying a range for string values, it is assumed that the strings are ordered alphabetically. Depending on the database settings, case sensitivity may also apply, so you need to be careful when designing and coding. For example, 'a' and 'A' may be treated as different values.

6. Important Notes When Using BETWEEN

Things to Watch Out for When Defining Ranges

When using the BETWEEN operator, you must pay attention to the order of the start and end values. If the start value is greater than the end value, the query may return unexpected results.

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

Also, because BETWEEN includes both endpoints of the specified range, it is important to choose the start and end values carefully.

Index Usage and Efficiency

The BETWEEN operator generally provides performance comparable to standard comparison operators. However, to optimize query performance, you need to set appropriate indexes. When indexes are configured correctly, queries using the BETWEEN operator can run faster. For example, adding an index to a date column allows date range queries to be executed more efficiently.

7. Practical Queries and Advanced Examples

Using BETWEEN with Multiple Columns

The BETWEEN operator can also be used by combining multiple columns. For example, if you want to perform a range search by specifying both product price and stock quantity at the same time, you can write a query like this:

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

This query searches for products whose price is within the range of 1,000 to 5,000 and whose stock is within the range of 50 to 200.

Practical Example of NOT BETWEEN

By using the negative form NOT BETWEEN, you can extract data that falls outside a specific range. For example, if you want to find employees whose salary is below 50,000 or above 100,000, you can write:

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

Execution Result：

This query retrieves employees whose salary does not fall within the range of 50,000 to 100,000. Using NOT BETWEEN makes it easy to extract data using the opposite condition.

8. Visual Example of a Query

To visually show query results, it can be effective to use a simple diagram. For example, you can use a diagram like the one below to explain how BETWEEN works:

Price range: [----- 1000 ---- 5000 -----]
Product A price: 3000 (in range)
Product B price: 6000 (out of range)

Using a diagram like this makes it easier to intuitively understand whether the data falls within the range specified in the query.

9. Summary

The BETWEEN operator is a very useful tool in MySQL when you need to search within a specified range. It can be applied to numeric, date, and string data, allowing you to write queries in a concise and efficient way. However, it is important to understand a few key points, such as the fact that it includes both endpoints of the range and how indexes can impact performance. By using this knowledge, you can optimize database query performance and efficiently extract the data you need.

10. References

For more details about queries and additional usage patterns, it is a good idea to refer to the official MySQL documentation and specialized books on databases. Also, by actually trying queries yourself, you can deepen your understanding of the BETWEEN operator.