MySQL BETWEEN Operator

The BETWEEN operator used to retrieve values within a range in to compare . We frequently use the BETWEEN operator in the WHERE clause of the SELECT, INSERT, UPDATE, and DELETE statements.

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)

Blog

mysql-between

MySQL BETWEEN Operator