EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL BETWEEN
 

MySQL BETWEEN

Priya Pedamkar
Article byPriya Pedamkar

Updated May 10, 2023

MySQL BETWEEN

 

 

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

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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

MySQL BETWEEN output 1

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:

MySQL BETWEEN output 2

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:

MySQL BETWEEN output 3

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:

output 4

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:

output 5

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:

output 6

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.

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW