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;
4.5 (7,329 ratings)
View Course
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 | 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 –
