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.
Table structure Used for Example explanation
+————-+———-+
| Field | Type |
+————-+———-+
| ID | int(11) |
| Name | char(35) |
| CountryCode | char(3) |`
| District | char(20) |
| Population | int(11) |
+————-+———-+
Example for MYSQL Limit
select * from city where population between 300000 and 2700000;
Output of Above Example
+—-+————-+————-+—————+————+
| ID | Name | CountryCode | District | Population |
+—-+————-+————-+—————+————+
| 1 | Kabul | AFG | Kabol | 1780000 |
| 5 | Amsterdam | NLD | Noord-Holland | 731200 |
| 6 | Rotterdam | NLD | Zuid-Holland | 593321 |
| 7 | Haag | NLD | Zuid-Holland | 440900 |
| 35 | Alger | DZA | Alger | 2168000 |
| 36 | Oran | DZA | Oran | 609823 |
| 37 | Constantine | DZA | Constantine | 443727 |
+—-+————-+————-+—————+————+
7 rows in set (0.00 sec)