BETWEEN Operator in SQL

BETWEEN Operator in SQL

3 mins read187 Views Comment
clickHere
Vikram
Vikram Singh
Assistant Manager - Content
Updated on Nov 22, 2022 19:40 IST

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.

2022_07_SQL-OPERATORS-1.jpg

Table of Content

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_names
FROM table_name
WHERE column_name BETWEEN value_1 AND value_2;
Copy code

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
Employee

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 Employee
WHERE CTC BETWEEN 5 AND 10;
Copy code

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 Employee
WHERE CTC NOT BETWEEN 5 AND 10;
Copy code

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, CTC
FROM Employee
WHERE Name BETWEEN ‘Dheeraj’ AND ‘Garima’
ORDER BY Name;
Copy code

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 Joining
FROM Employee
WHERE Date of Joining BETWEEN2021-04-01AND2021-08-30;
Copy code

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 Joining
FROM Employee
WHERE CTC BETWEEN 5 AND 10
AND Department IN (‘Sales’, ‘Admin’);
Copy code

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.

About the Author
author-image
Vikram Singh
Assistant Manager - Content

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

Comments