MySQL BETWEEN

Introduction to MySQL BETWEEN

In SQL we use different types of languages for different functionalities that carry different commands. So mostly while fetching records using Data query language, we use SELECT command. BETWEEN operators comes to picture when we want records/select records within the range of values. These values can either be numbers, date or in string format. Between operator is inclusive i.e. both start and end values are included in the result. In the same manner, we can also use NOT BETWEEN, which will fetch the records outside of the given range of values. In this topic, we are going to learn about MySQL BETWEEN

Syntax:

SELECT column_name FROM table_name
WHERE column_name BETWEEN value1 AND value2;

or

SELECT column_name FROM table_name
WHERE column_name NOT BETWEEN value1 AND value2;

• SELECT & FROM – Command will fetch records from the table
• WHERE – Command fetches records where our condition satisfies
• BETWEEN – This specifies the range of values
• NOT BETWEEN – `This will fetch records which are not in the specified range of values
• AND – This operator make sure the record should match both the conditions

How “BETWEEN” conditions work in MySQL?

Let’s discuss a very small example of how this condition works in real-time scenarios

For example: Create a product table with product details and price of them

Product_name	Price
Apple	20
Orange	25
Grapes	30
Banana	22
Pomegranate	28

If we will have to select those item names, whose price lies between 20 and 25, the below query can help us fetching those records.

SELECT product_name FROM product
WHERE price BETWEEN 20 AND 25;

It will look into the table for those particular records whose price will be between 20 to 25. As earlier we have mentioned this between operators is an inclusive one, it will include both 20 and 25

Output:

Product_name	Price
Apple	20
Orange	25
Banana	22

SELECT product_name FROM product
WHERE price NOT BETWEEN 20 AND 25;

It will look into the table for those particular records whose price will not be between 20 to 25. As earlier we have mentioned this between operators is an inclusive one, it will not include both 20 and 25

Output:

Product_name	Price
Grapes	30
Pomegranate	28

SELECT product_name FROM product
WHERE price BETWEEN 25 AND 30;

It will look into the table for those particular records whose price will be between 25 to 30. As earlier we have mentioned this between operators is an inclusive one, it will include both 25 and 30

Output:

Product_name	Price
Orange	25
Grapes	30
Pomegranate	28

Examples to implement MySQL BETWEEN condition:

Here we will create another table “employee” with the following attributes.

Employee_id	First_name	Last_name	Email	Hire_date	Salary
100	ste abcd	King	SKING	1987-06-17	24000.00
101	Neena	Kochhar	NKOCHHAR	1989-09-21	17000.00
102	Lex	De Haan	LDEHAAN	1993-01-13	17000.00
108	Nancy	Greenberg	NGREENBE	1994-08-17	12000.00
114	Den	Raphaely	DRAPHEAL	1994-12-07	11000.00
145	John	Russell	JRUSSEL	1996-10-01	14000.00
146	Karen	Partners	KPARTNER	1997-01-05	13500.00
147	Alberto	Errazuriz	AERRAZUR	1997-03-10	12000.00
148	Gerald	Cambrault	GCAMBRAU	1999-10-15	11000.00
149	Eleni	Zlotkey	EZLOTKEY	2000-01-29	10500.00
162	Clara	Vishney	CVISHNEY	1997-11-11	10500.00
168	Lisa	Ozer	LOZER	1997-03-11	11500.00
174	Ellen	Abel	EABEL	1996-05-11	11000.00
201	Michael	Hartstein	MHARTSTE	1996-02-17	13000.00
205	Shelley	Higgins	SHIGGINS	1994-06-07	12000.00

Example #1

Here we will find out the employees having a salary between 12000 to 20000.

SELECT * FROM employee
WHERE salary BETWEEN 12000 AND 20000;

Output:

Here we will fetch out those employees having a salary, not between 12000 to 20000.

SELECT * FROM employee
WHERE salary NOT BETWEEN 12000 AND 20000;

Output:

Example #2

We will fetch employees whose hire date is between 1/1/1990 to 1/1/1995

SELECT * FROM employee
WHERE salary BETWEEN ‘1990-1-1’ AND ‘1995-1-1’ ;

Output:

We will fetch employees whose hire date is not between 1/1/1990 to 1/1/1995

SELECT * FROM employee
WHERE salary NOT BETWEEN ‘1990-1-1’ AND ‘1995-1-1’ ;

Output:

Example #3

Again will fetch those records, whose first name starts with the alphabet from A to H.

SELECT * FROM employee
WHERE SUBSTR(first_name,1,1) BETWEEN ‘a’ AND ‘h’ ;

SUBSTR(column_name, start_index, end_index) finds out the sub string within another string by specifying the starting and ending index. If in the name ‘Salman’ we give substr(name,1,1), it will fetch the alphabet ‘S’.

Output:

Same will find out whose name doesn’t start from alphabet from A to H

SELECT * FROM employee
WHERE SUBSTR(first_name,1,1) NOT BETWEEN ‘a’ AND ‘h’ ;

Output:

Conclusion

Between operators is very useful in many cases where we need to get/ fetch value in certain ranges for analytics or visualization purposes. The above-explained example is also from a real-life scenario. If we want to find out our highest-paid employees or those employees joined in specific time duration, we need to understand the significance of between operators.

Recommended Articles

This is a guide to MySQL BETWEEN. Here we discuss How “BETWEEN” conditions work in MySQL along with the respective query examples and outputs. You may also look at the following article to learn more –

1. MySQL Queries
2. Wildcards in MySQL
3. MySQL Server
4. NOT in MySQL