MySQL BETWEEN Operator

The MySQL Between Operator returns the rows whose values are between the given two values or range. For example, you can use this MySQL Between operator to find the Sales between Jan 2018 to Dec 2018 Dates.

MySQL Between Operator Syntax

The basic syntax of the MySQL Between operator can write as:

SELECT Column_Names 
FROM Table_Name
WHERE Column_Value BETWEEN Value1 AND Value2

The BETWEEN operator displays the records available between the Value1 and Value2 Including them. It mean, Column_Value >= Value1 and Column_Value <= Value2.

MySQL Between example

This simple example shows the working functionality of MySQL Between on Numerical values. In the first statement, 2 is between 1 and 3, and 7 is between 1 and 9. So, it returns 1. In the next statement, 4 is not between 1 and 3, and 22 is not between 1 and 9 so, it returns 0.

SELECT 2 BETWEEN 1 AND 3, 7 BETWEEN 1 AND 9;

SELECT 4 BETWEEN 1 AND 3, 22 BETWEEN 1 AND 9;

SELECT 2 BETWEEN 3 AND 1, 7 BETWEEN 9 AND 1;

In this example, we show how to use this MySQL Between operator on String data. The first statement returns 1, 1 because b is between a and c, and t is between s and v. Within the second MySQL statement, it converts the string 10 to int and checks whether 8 is between 4 and 10.

SELECT 'b' BETWEEN 'a' AND 'c', 't' BETWEEN 's' AND 'v';

SELECT 8 BETWEEN 4 AND '10';

SELECT 8 BETWEEN 4 AND 'Hi';

MySQL Between Operator On Numeric and String Data Example

For this MySQL Between operator demonstration, We are going to use the Customer table. The following MySQL screenshot shows you the data present inside this table

The following query returns the Customers whose Income is between 50000 and 80000. Or, whose Income is greater than or equal to 50000 and less than or equal to 80000.

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
 FROM `MySQL Tutorial`.customer
 WHERE Income BETWEEN 50000 AND 80000;

This MySQL Between operator example finds all the Customers whose Last Name is between Erickson and Suresh

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
FROM `MySQL Tutorial`.customer
WHERE `Last Name` BETWEEN 'Erickson' AND 'Suresh';

TIP: We can also use a single character instead of writing the complete name. It is because this Between function considers the first character as a reference.

MySQL Between Dates Example

In this MySQL Dates Between example, we find all the Customers whose Hire Date is between 2009-01-01 and 2013-01-15

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
FROM `MySQL Tutorial`.customer
WHERE HireDate BETWEEN '2009-01-01 ' AND '2013-01-15';

This MySQL Between Dates example finds and returns all the Customers whose Hire Date is between 2009-01-01 and 2013-01-15 and Income between 60000 and 90000.

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
FROM `MySQL Tutorial`.customer
WHERE HireDate BETWEEN '2009-01-01 ' AND '2013-01-15' AND
      Income BETWEEN 60000 AND 90000;