MySQL Between Operator With Examples

The MySQL Between clause is used to get a range of values between the values specified.

MySQL Between syntax

The basic syntax for Between is,

SELECT <column-names> 
FROM <table-name>
WHERE <column_name> BETWEEN <value1> AND <value2>;

MySQL 'Between' Example with numbers

We can do a Between with two numeric values. This returns all the records between these two values with the values included. All other values for that column are filtered out.

select * from film where rental_duration between 4 AND 5;

MySQL Between with Strings/Text

Similarly, we can also do a between with two string values. This returns all the records between these values with the values included. All other values for that column are filtered out.

select * from country where country between 'Afghanistan' AND 'Canada';

MySQL NOT Between

The Not between does the exact opposite of the between clauses. The result contains all the records except the values between the two values.

For numeric values,

select * from film where rental_duration NOT between 4 AND 5;

For string/text values,

select * from country where country not between 'Afghanistan' AND 'Canada';