If you work with MySQL databases, sometimes you may need to retrieve data based on a specified range of values for a particular column. This is where the “BETWEEN” operator in MySQL comes into play. By using this operator, users can filter the table’s records based on a range of numeric values or dates.

This guide will explain the use of the “BETWEEN” operator in MySQL using practical examples.

How to Use the “BETWEEN” Operator in MySQL?

In MySQL, the “BETWEEN” operator is utilized to retrieve the rows from a table based on a specified range of values for a particular column. The syntax of defining the range with the “BETWEEN” operator is depicted in the following snippet:

In the above syntax, the “BETWEEN” operator returns 1, if the value is equal or greater than “start” and equal or smaller than “end”.

Let’s move to the examples to understand the use cases of the “BETWEEN” operator in MySQL.

Example 1: Check the Static Number Value in a Specific Range
The “BETWEEN” operator can be utilized with the “SELECT” statement to check the number value in a particular range. The example to check, if the “50” exists in a range from “10” to “100” is given below:

In the above code, the range is defined with the “AND” operator. If the output from the above query is “1”, it means that “50” exists within the given range. On the other hand, if the output is “0”, it means that “50” does not fall within the specified range.

Output

The output displayed that the value “50” exists within the given range (i.e., “10” to “100”).

Example 2: Use BETWEEN Operator With WHERE Clause
The “BETWEEN” operator can be utilized with the “WHERE” clause to filter the table’s data based on the values specified within the BETWEEN operator. An example of getting the rows that have the “customer_id” column value from “2” to “3” of the “lh_orders” table is given below:

Output

The output showed the rows that have the “customer_id” value from “2” to “3”.

Example 3: Use BETWEEN Operator to Retrieve Data Within a Specified Date Range
The stated operator can also be utilized to retrieve data within a specified date range. The example to retrieve “joineddate” column data based on the specified date range is given below:

In the above example, the date range is from “2022-07-01” to “2022-12-15”.

Output

The output displayed the data that lies within the specified date range.

Example 4: Use BETWEEN Operator With UPDATE Query
The “UPDATE” statement is utilized to update or modify the already stored table’s values. While the “BETWEEN” operator can be used with the “UPDATE” statement to update the values of the specific column within a particular range. The example to update the “quantity” column values of the “orders_details” table is given below:

In the above example, the quantity column will be updated to “2” if the quantity is between “13” to “18”.

Output

The output showed that the value has been updated according to the specified condition.

Conclusion

The “BETWEEN” operator is a useful comparison operator in MySQL that allows us to retrieve data based on a specified range of values for a particular column. It can be used in various scenarios, such as checking if a value falls within a particular range, filtering a table’s data based on a range, retrieving data within a specified date range, and updating values within a specified range. This blog has presented detailed information on the usage of MySQL’s BETWEEN operator.