BETWEEN Operator in SQL

BETWEEN Operator in SQL

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

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

Syntax

` `
`SELECT column_namesFROM table_nameWHERE 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.

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;Copy code`

Output

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;Copy code`

Output

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;Copy code`

Output

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’;Copy code`

Output

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’);Copy code`

Output

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.