MySQL BETWEEN

The MySQL BETWEEN operator is a logical operator that is used to filter the results of a query based on a specified range of values. It is commonly employed in the WHERE clause of a SELECT statement to retrieve rows with values falling within a particular range.

Syntax

The basic syntax of the BETWEEN operator is as follows:

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

Here, column_name is the name of the column you want to filter, table_name is the name of the table containing that column, and value1 and value2 define the range. The BETWEEN operator is inclusive, meaning that it includes both value1 and value2 in the result set.

Example

For example, let’s say we have a table named employees with a column named salary, and we want to retrieve employees with salaries between $40,000 and $60,000. The query would look like this:

SELECT *
FROM employees
WHERE salary BETWEEN 40000 AND 60000;

This query will return all rows from the employees table where the salary column falls within the specified range.

It’s important to note that the BETWEEN operator can be used with various data types, including numbers, dates, and strings. The values used with the BETWEEN operator must be of the same data type or compatible data types.

Additionally, you can use the NOT BETWEEN operator to retrieve rows outside a specified range. For example:

SELECT *
FROM employees
WHERE salary NOT BETWEEN 40000 AND 60000;

This query will return all rows from the employees table where the salary column is not within the range of $40,000 and $60,000.

In summary, the MySQL BETWEEN operator is a useful tool for filtering data based on a specified range, providing a concise and efficient way to retrieve relevant information from a database.