BETWEEN Operator in SQL
In the previous articles, we have discussed LIKE and IN operator. In this article we will discuss BETWEEN operator in SQL that is also a logical operator.
Table of Content
Best-suited Data Analytics courses for you
Learn Data Analytics with these high-rated online courses
What is the BETWEEN OPERATOR?
BETWEEN operator is a logical operator in SQL that is used to select the values from the tables within the given range. The values can be the date, number, or text.
- BETWEEN operator is an inclusive operator i.e. beginning and ending both values are included.
- It can be used with SELECT, INSERT, UPDATE, and DELETE command
- It is a shorthand for >= AND <=
Must Read: SQL LIKE
Must Read: SQL IN
Syntax
SELECT column_namesFROM table_nameWHERE column_name BETWEEN value_1 AND value_2;
Now, we will take some examples to get a better understanding of BETWEEN operators.
Here, we will take the Employee Dataset (that contains Employee ID, Name, Gender, Department, Education, Date of Joining, and CTC) to do some examples to get a clear understanding of different logical operators.
| EmployeeID | Name | Gender | Department | Education | Date of Joining | CTC(in Lacs) |
| 1001 | Ajay | M | Engineering | Doctoral | 2021-01-07 | 25 |
| 1002 | Babloo | M | Engineering | UG | 2021-02-14 | 23 |
| 1003 | Chhavi | F | HR | PG | 2021-03-21 | 15 |
| 1004 | Dheeraj | M | HR | UG | 2021-04-17 | 12 |
| 1005 | Evina | F | Marketing | UG | 2021-05-17 | 16 |
| 1006 | Fredy | M | Sales | UG | 2021-06-13 | 10 |
| 1007 | Garima | F | Sales | PG | 2021-07-18 | 10 |
| 1008 | Hans | M | Admin | PG | 2021-08-15 | 8 |
| 1009 | Ivanka | F | Admin | Intermediate | 2021-09-09 | 7 |
| 1010 | Jai | M | Peon | 2021-10-05 | 4 |
Must Read: Introduction to SQL
Must Read: SQL ACID Properties
BETWEEN condition with Numeric Values
Example 1: Find the employee details whose CTC is between 5 and 10 lacs.
Query
SELECT *FROM EmployeeWHERE CTC BETWEEN 5 AND 10;
Output
| EmployeeID | Name | Gender | Department | Education | Date of Joining | CTC(in Lacs) |
| 1006 | Fredy | M | Sales | UG | 2021-06-13 | 10 |
| 1007 | Garima | F | Sales | PG | 2021-07-18 | 10 |
| 1008 | Hans | M | Admin | PG | 2021-08-15 | 8 |
| 1009 | Ivanka | F | Admin | Intermediate | 2021-09-09 | 7 |
Must Read: What is the difference between SQL and MySQL?
Must Read: Difference between SQL and NoSQL
NOT with BETWEEN
Example 2: Find the employee details whose CTC is not between 5 and 10 lacs.
SELECT *FROM EmployeeWHERE CTC NOT BETWEEN 5 AND 10;
Output
| EmployeeID | Name | Gender | Department | Education | Date of Joining | CTC(in Lacs) |
| 1001 | Ajay | M | Engineering | Doctoral | 2021-01-07 | 25 |
| 1002 | Babloo | M | Engineering | UG | 2021-02-14 | 23 |
| 1003 | Chhavi | F | HR | PG | 2021-03-21 | 15 |
| 1004 | Dheeraj | M | HR | UG | 2021-04-17 | 12 |
| 1005 | Evina | F | Marketing | UG | 2021-05-17 | 16 |
| 1010 | Jai | M | Peon | 2021-10-05 | 4 |
Must Read: Subqueries in SQL
Must Read: SQL CREATE TABLE
BETWEEN with Text Values
Example 3: Find the EmployeeID, Name, and CTC of the employees whose name belonging to the range ‘Dheeraj’ to ‘Garima’
Query
SELECT EmployeeID, Name, CTCFROM EmployeeWHERE Name BETWEEN ‘Dheeraj’ AND ‘Garima’ORDER BY Name;
Output
| EmployeeID | Name | CTC (in Lacs) |
| 1004 | Dheeraj | 12 |
| 1005 | Evina | 16 |
| 1006 | Fredy | 10 |
| 1007 | Garima | 10 |
Must Read: SQL WHERE
Must Read: Introduction to Normalization
BETWEEN Condition with Date
Example 4: Find the Employee ID, Name, and Department of the employees who joined between 2021-04-01 and 2021-08-30.
Query
SELECT EmployeeID, Name, Department, Date of JoiningFROM EmployeeWHERE Date of Joining BETWEEN ‘2021-04-01’ AND ‘2021-08-30’;
Output
| EmployeeID | Name | Department | Date of Joining |
| 1004 | Dheeraj | HR | 2021-04-17 |
| 1005 | Evina | Marketing | 2021-05-17 |
| 1006 | Fredy | Sales | 2021-06-13 |
| 1007 | Garima | Sales | 2021-07-18 |
| 1008 | Hans | Admin | 2021-08-15 |
Must Read: SQL LIMITS
Must Check: SQL Online Course and Certifications
BETWEEN with IN
Example 5: Find the Employee ID, Name, and Department of the employees whose salary is between 5 and 10 lacs and working in sales and admin department.
Query
SELECT EmployeeID, Name, Department, Date of JoiningFROM EmployeeWHERE CTC BETWEEN 5 AND 10AND Department IN (‘Sales’, ‘Admin’);
Output
| EmployeeID | Name | Department | Month of Joining |
| 1006 | Fredy | Sales | 2021-06-13 |
| 1007 | Garima | Sales | 2021-07-18 |
| 1008 | Hans | Admin | 2021-08-15 |
| 1009 | Ivanka | Admin | 2021-09-09 |
Must Read: SQL Comparison and Arithmetic Operator
Conclusion
In this article, we have discussed how to use BETWEEN operator in SQL with the help of examples.
Hope this article, will help you in your Data Science/Data Analysis journey.
Vikram has a Postgraduate degree in Applied Mathematics, with a keen interest in Data Science and Machine Learning. He has experience of 2+ years in content creation in Mathematics, Statistics, Data Science, and Mac... Read Full Bio
Top Picks & New Arrivals
