mysql-between
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.