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 operator to find the Sales between Jan 2018 to Dec 2018 Dates.

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

SELECT Column_Names 
FROM Table_Name
WHERE Column_Value BETWEEN Value1 AND Value2

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

MySQL Between Operator example

This simple example shows the working functionality of MySQL Between on Numerical values. In the first statement, 2 is from 1 to 3, and 7 is from 1 to 9. So, it returns 1. In the next statement, 4 is not in the middle of 1 and 3, and 22 is not in the middle of 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 in the middle of a and c, and t is in the middle of s and v. Within the second statement, it converts the string 10 to int and checks whether 8 is betwixt 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 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 in the middle of 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 customer
 WHERE Income BETWEEN 50000 AND 80000;

This example finds all the Customers whose Last Name is from Erickson to Suresh

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
FROM 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 function considers the first character as a reference.

MySQL Between Dates Example

In this Dates example, we find all the Customers whose Hire Date is from 2009-01-01 to 2013-01-15

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
FROM 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 in the middle of 2009-01-01 and 2013-01-15 and Income in the middle of 60000 and 90000.

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