Updated May 10, 2023
Introduction to MySQL BETWEEN
In SQL, we use different types of languages for other functionalities that carry various commands. So mostly, while fetching records using Data query language, we use the SELECT command. BETWEEN operators comes into the picture when we want records/select records within the range of values. These values can either be numbers, dates,, or in string format. Between operators is inclusive,, i.e., both start and end values are included in the result. Similarly, we can also use NOT BETWEEN, which will fetch the records outside 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 that are not in the specified range of values
- AND – This operator makes sure the record should match both the conditions
How “BETWEEN” conditions work in MySQL?
Let’s discuss a minimal 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 have to select those item names whose price lies between 20 and 25, the below query can help us fetch 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 records whose price will not be between 20 and 25. As earlier we 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 records whose price will be between 25 and 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 the sub-stringg within another string by specifying the starting and ending index. If we give substr(name,1,1) in the name’ Salman’, it will fetch the alphabet ‘S’.
Output:
Same will find out whose name doesn’t start from the 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 when we need to get/ fetch value in specific 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 a specific time duration, we need to understand the significance of between operators.
Recommended Articles
We hope that this EDUCBA information on “MySQL BETWEEN” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
