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