MySQL BETWEEN Operator Explained: Syntax, Examples, and Best Practices

1. Introduction

One of the important elements for executing queries efficiently in a MySQL database is the BETWEEN operator. This operator is extremely useful when checking whether data falls within a specific range. It is used to specify ranges for numbers, dates, and strings, enabling efficient data extraction in database searches and filtering. In this article, we will explain in detail how to use the MySQL BETWEEN operator, practical examples, and important considerations.

2. Basic Syntax of the BETWEEN Operator

BETWEEN Operator Overview

The BETWEEN operator is a condition used in the WHERE clause to check whether a specific column value falls within a defined range. The 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 employee ages, you can use this operator to extract employees within a specific age range.

Negative Form: NOT BETWEEN

If you want to search for values that are not included within a specified range, use the negative form NOT BETWEEN.

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

3. Specifying a Numeric Range

Using BETWEEN for Numeric Ranges

The BETWEEN operator is very convenient for specifying numeric ranges. For example, if you want to extract data where employee salaries are between 50,000 and 100,000, write the query as follows:

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

Sample Data:

Execution Result:

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

Difference from Comparison Operators

If you express 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. Especially when multiple range conditions are involved, BETWEEN makes the query easier to understand.

4. Specifying a Date Range

Using BETWEEN with Date Data

BETWEEN can also be used to specify date ranges. For example, to extract orders from the orders table between January 1, 2024 and December 31, 2024, write the query as follows:

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 within the specified date range.

Handling Time Data

If the date data includes time, special attention is required. For example, if the order_date column is of type DATETIME and includes time values, specifying a range with BETWEEN will include records from midnight at the start date to 23:59:59 on the end date.

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

5. Specifying a String Range

Using 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 between ‘A’ and ‘M’, write the query as follows:

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

Sample Data:

Execution Result:

Alphabetical Order Considerations

When specifying string ranges, it is assumed that strings are ordered alphabetically. Since databases may distinguish between uppercase and lowercase letters depending on collation settings, caution is required. For example, 'a' and 'A' may be treated as different values.

6. Important Considerations When Using BETWEEN

Range Order Matters

When using the BETWEEN operator, be careful about the order of the start and end values. If the start value is greater than the end value, the query may return unintended results.

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

Also, since BETWEEN includes both boundary values, it is important to choose start and end values carefully.

Index Usage and Efficiency

The BETWEEN operator generally has performance comparable to standard comparison operators. However, to optimize query performance, proper indexing is essential. When indexes are correctly configured, query execution speed using the BETWEEN operator improves. For example, setting an index on a date column allows date range queries to run more efficiently.

7. Practical Queries and Advanced Examples

Using BETWEEN with Multiple Columns

The BETWEEN operator can also be used in combination across multiple columns. For example, when performing a range search by specifying both product price and stock quantity, the following query can be used:

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

This query searches for products with prices between 1,000 and 5,000 and stock quantities between 50 and 200.

NOT BETWEEN Advanced Example

By using the negative form NOT BETWEEN, you can extract data outside a specified range. For example, to search for employees whose salaries are below 50,000 or above 100,000, write the query as follows:

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

Execution Result:

This query retrieves employees whose salaries do not fall within the range of 50,000 to 100,000. Using NOT BETWEEN makes it easy to extract data based on inverse conditions.

8. Visual Query Example

To visually demonstrate query results, using a simple diagram can be effective. For example, the following illustration explains how BETWEEN works:

Price range: [----- 1000 ---- 5000 -----]
Product A price: 3000 (within range)
Product B price: 6000 (outside range)

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

9. Summary

The BETWEEN operator is a highly useful tool in MySQL when performing range-based searches. It can be applied to numeric, date, and string data, allowing queries to be written concisely and efficiently. However, it is important to understand key considerations such as inclusion of boundary values and proper index usage. By leveraging this knowledge, you can optimize database query performance and efficiently extract the data you need.

10. References

For more details and additional usage examples, refer to the official MySQL documentation and specialized database textbooks. Practicing queries yourself will further deepen your understanding of the BETWEEN operator.