MySQL consists of various operators for performing efficient data queries, and the BETWEEN operator is a key operator for filtering records within a specific range. By specifying a lower and upper bound, BETWEEN helps you easily retrieve data that falls between two values. This operator simplifies queries involving ranges of numbers or dates, making data analysis more simple and effective.

In this article, we will explore the BETWEEN operator, highlighting its usage and examples.

MySQL BETWEEN Operator

The BETWEEN operator in MySQL is used to filter results within a specified range, including both boundary values. It simplifies queries by allowing you to set a lower and upper limit for numerical, date, or text data. This operator is useful for retrieving data that falls within a particular range efficiently.

Syntax:

The syntax for using BETWEEN Operator in MySQL is as follows:

column_name BETWEEN value1 AND value2;

Parameters:

• column_name: The column to be filtered.
• value1: The lower bound of the range.
• value2: The upper bound of the range.

MySQL BETWEEN Operator Examples

Let’s look at some examples of the BETWEEN Operator in MySQL. Learning the BETWEEN Operator with examples will help in understanding the concept better.

First, let’s create a table:

Demo MySQL Tables

We create the first table “employees_2023” in this example.

CREATE TABLE employees_2023 (
    employee_id INT,
    name VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees_2023 (employee_id, name, salary) VALUES
(1, 'Gaurav', 75000.00),
(2, 'Yuvraj', 82000.00),
(3, 'Shruti', 69000.00);

SELECT * FROM employees_2023;

Output:

+-------------+--------+----------+
| employee_id | name   | salary   |
+-------------+--------+----------+
|           1 | Gaurav | 75000.00 |
|           2 | Yuvraj | 82000.00 |
|           3 | Shruti | 69000.00 |
+-------------+--------+----------+

To create the second table “employees_2024“, write the following SQL queries:

CREATE TABLE employees_2024 (
    employee_id INT,
    name VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees_2024 (employee_id, name, salary) VALUES
(4, 'Anjali', 90000.00),
(5, 'Vaishnavi', 85000.00),
(6, 'Gauri', 78000.00);

SELECT * FROM employees_2024;

Output:

+-------------+-----------+----------+
| employee_id | name      | salary   |
+-------------+-----------+----------+
|           4 | Anjali    | 90000.00 |
|           5 | Vaishnavi | 85000.00 |
|           6 | Gauri     | 78000.00 |
+-------------+-----------+----------+

Example 1: Filter Employees with Salary Between 70000 and 80000

In this example, we are using the BETWEEN operator to retrieve employees from the employees_2023 table whose salaries fall between 70,000 and 80,000. This query helps identify employees within this specific salary range.

SELECT * FROM employees_2023 WHERE salary BETWEEN 70000 AND 80000;

Output:

+-------------+--------+----------+
| employee_id | name   | salary   |
+-------------+--------+----------+
|           1 | Gaurav | 75000.00 |
+-------------+--------+----------+

Example 2: Filter Employees with Salary Between 75000 and 85000

In this example, we are using the BETWEEN operator to find employees in the employees_2024 table with salaries ranging from 75,000 to 85,000. This query highlights employees whose salaries fall within this range.

SELECT * FROM employees_2024 WHERE salary BETWEEN 75000 AND 85000;

Output:

+-------------+-----------+----------+
| employee_id | name      | salary   |
+-------------+-----------+----------+
|           5 | Vaishnavi | 85000.00 |
|           6 | Gauri     | 78000.00 |
+-------------+-----------+----------+

Example 3: Filter Employees with Salary Not in the Range of 70000 to 80000

In this example, we are using the NOT BETWEEN operator to select employees from the employees_2023 table whose salaries do not fall between 70,000 and 80,000. This query filters out those outside the specified salary range.

SELECT * FROM employees_2023 WHERE salary NOT BETWEEN 70000 AND 80000;

Output:

+-------------+--------+----------+
| employee_id | name   | salary   |
+-------------+--------+----------+
|           2 | Yuvraj | 82000.00 |
|           3 | Shruti | 69000.00 |
+-------------+--------+----------+

Conclusion

In conclusion, the MySQL BETWEEN operator is an useful Operator for filtering records within a specific range, whether for numerical values, dates, or text. Its ability to simplify queries by setting lower and upper limits makes data retrieval more efficient and precise. Using this operator effectively can enhance your database queries and improve data analysis.

FAQs on MySQL BETWEEN Operator

Can the BETWEEN operator be used with non-numerical data?

Yes, the BETWEEN operator can be used with dates and text data as well. For text, it returns values in alphabetical order between the specified range.

Does the BETWEEN operator include the boundary values?

Yes, the BETWEEN operator is inclusive, meaning it includes both the lower and upper boundary values in the results.

How does NOT BETWEEN work?

The NOT BETWEEN operator filters records that fall outside the specified range. It excludes values that lie between the given lower and upper bounds.

Can you use BETWEEN with multiple columns?

No, the BETWEEN operator works with a single column at a time. To filter based on multiple columns, you need to use separate BETWEEN conditions with AND or OR.

Is the BETWEEN operator case-sensitive?

For text data, the BETWEEN operator’s case sensitivity depends on the collation of the column. By default, MySQL text comparisons are case-insensitive.