MySQL BETWEEN Operator

The MySQL Between Operator returns the records (or rows) whose values are between the given two values or range.

For example, to find the Sales between Jan 2018 to Dec 2018, you can use this MySQL Between Dates.

MySQL Between Operator Syntax

The basic syntax of the MySQL Between operator can be written 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.

In this article we will show you, How to use the MySQL Between Operator with an examples.

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, they return 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;

OUTPUT

In this example, we will show you, How to use this 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 testament, it will convert the string 10 to int and check 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';

OUTPUT

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 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;

OUTPUT

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 single character instead of writing the complete name. Because, this function considers the first character as a reference.

OUTPUT

MySQL Between Dates Example

In this MySQL Dates Between example, we will 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';

OUTPUT

This MySQL Between Dates example find 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;

OUTPUT

Thank You for Visiting Our Blog