The BETWEEN Operator in MySQL

• is used to retrieve records based on the given range
• The MySQL BETWEEN operator can be used with SELECT, DELETE, UPDATE and INSERT statements.
• The two values provided in the BETWEEN operator are inclusive. For example, BETWEEN 10 and 20 means retrieving records that fall between these two values including 10 and 20.
• You may use numbers, dates and text values in the BETWEEN operator.
• The BETWEEN operator is used with the AND operator (as shown in the examples in the coming section).
• The BETWEEN is equivalent to (min <= expr AND expr <= max).

The syntax for using the BETWEEN operator

The general way of using the BETWEEN operator is:

expression [NOT] BETWEEN Min AND Max;

An example of  BETWEEN with numbers

In this example, I used two numbers to define the min and max values in the BETWEEN operator. For that, we have a table tst_employees that stores employee data. This table has a salary column that is a numeric type.

The query below returns the records of those employees whose salary is between 3000 to 5000 (inclusive).

Query:

SELECT *

FROM tst_employess

WHERE employee_salary

BETWEEN 3000

AND 5000

The result-set:

It can be seen in the above result-set that a record with 3000 value is also included in the result which is equal to the minimum given value. While a record with 5000.5 is not included as this is more than the max given value.

Using dates in the BETWEEN operator

The following query uses two dates in the BETWEEN operator. We will fetch the records of those employees who joined between two given dates:

Query:

SELECT *

FROM tst_employess

WHERE employee_join_date

BETWEEN  '2016-02-16'

AND  '2016-10-23'

The result-set:

The record-set above shows Mikes’ records whose joining date is equal to our min and max given dates in the BETWEEN operator.

The example of using text

You may also specify the range of values as text using the BETWEEN operator. The following example provides two text value:

The query:

SELECT *

FROM tst_employess

WHERE employee_name

BETWEEN  'C'

AND  'M'

Result-set:

The first record-set shows complete table data while the second one is returned by our demo query.

Using BETWEEN with UPDATE statement

As such in the UPDATE statement, you may use the WHERE clause for specifying the records to modify. In the WHERE clause, you may use BETWEEN operator just like the SELECT statement for filtering the records.

See the query below where I used the UPDATE statement with the BETWEEN clause for selecting the records:

The query:

UPDATE tst_employess

SET employee_salary = 4200

WHERE id BETWEEN 2 AND 4;

The result after UPDATE command:

You can see the salary column updated for the IDs 2, 3 and 4.