MySQL BETWEEN Operator

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.