MySQL BETWEEN Operator

The BETWEEN operator in MySQL is used to retrieve rows based on a range of values. It is commonly used in the WHERE clause of a query to filter rows based on a range of numeric, date, or time values.

The syntax for the BETWEEN operator is as follows:

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

In this syntax, column_name(s) is the name of the column or columns that you want to retrieve data from, table_name is the name of the table that contains the data, value1 is the lower bound of the range, and value2 is the upper bound of the range.

Example:

Suppose we have a table named employees that contains employee data such as employee_id, employee_name, and salary. We want to retrieve all the employees who have a salary between $50000 and $70000. We can use the BETWEEN operator to achieve this as shown below:

SELECT *
FROM employees
WHERE salary BETWEEN 50000 AND 70000;

Output:

+-------------+---------------+--------+
| employee_id | employee_name | salary |
+-------------+---------------+--------+
| 1           | John Doe      | 60000  |
| 3           | Mary Johnson | 65000  |
| 5           | William Brown | 55000  |
+-------------+---------------+--------+

In this example, we use the BETWEEN operator to check if the salary column falls between $50000 and $70000. The output shows all the employees who have a salary in this range.

We can also use the NOT BETWEEN operator to retrieve all the rows where the column does not fall within the specified range. For example:

SELECT *
FROM employees
WHERE salary NOT BETWEEN 50000 AND 70000;

Output:

+-------------+---------------+--------+
| employee_id | employee_name | salary |
+-------------+---------------+--------+
| 2           | Jane Smith    | 80000  |
| 4           | Peter Johnson | 45000  |
+-------------+---------------+--------+

In this example, we use the NOT BETWEEN operator to check if the salary column does not fall between $50000 and $70000. The output shows all the employees who have a salary outside of this range.

In conclusion, the BETWEEN operator in MySQL is a useful tool for filtering rows based on a range of values. It allows developers to easily retrieve data that falls within a specific range, making it a powerful tool for data analysis and management.