Introduction
This article gives an explanation about how to find the particular records from the range in the SQL server as well as shows you how to gets rows between a specific range, with a single select statement using BETWEEN operator in SQL server. Here, I'll also explain what is BETWEEN operator in SQL Server, the use of BETWEEN operator in SQL Server, how to selects values within a given range using BETWEEN operator in SQL server.
In my previous articles, I explained how to find the last date of any month in sql server, split comma separated string using string_split in sql server, cursor in sql server with syntax and example, error-sql server restore database error | alter database mode single user to multi-user and delete/remove duplicate records from table using cte (common table expression) in sql server.
Recently, a day ago one of my friend in the office trying to get values from given specific integer range, actually he didn't work with any data-driven applications before, so he didn't have much knowledge of databases and supported operators of the SQL server, so he told me that he wants to return rows between a given specific range in SQL server, I suggest him to use BETWEEN operator in the where clause of select statement, he tried and it works for him and he got the expected result.
Finally, he told me that please explain some basic information about the BETWEEN operator in SQL Server, how to use the BETWEEN operator, where and when we can use the BETWEEN operator and etc. I think many developers or students who are beginners didn't know about the BETWEEN operator in the SQL server, So I am writing this article for all those developers or students who is beginners, here I'll explain basic information about the BETWEEN operator in SQL Server with an example.
Requirement
1) What is a BETWEEN operator in SQL Server?
2) Explain the use of a BETWEEN operator with an example.
Implementation
What is a BETWEEN operator in SQL Server?
The BETWEEN operator in SQL server is used for the return particular record/values from a given specific range, The record/values can be a text, integer, date and etc.
Syntex
SELECT column_name(s)FROM table_nameWHERE column_name BETWEEN rangecolumn1 AND rangecolumn2;
So, now let's take a simple example of an employee management system, where we will find the salary slab of an employee based on the work experience of the employee.
Here, first, we will create a sample table of employee salary slab and insert a few sample records in the table for the demonstration.
Create Table
CREATE TABLE tbl_SalarySlabs ( [ID] int,[ExperienceFrom] int, [ExperienceTo] int, [SalarySlab] float )
Insert Records into Table
INSERT INTO tbl_SalarySlabs ([ID], [ExperienceFrom], [ExperienceTo],[SalarySlab]) VALUES (1, 0, 1, 10000), (2, 2, 4, 25000), (3, 5, 7,35000), (4, 8, 10,55000), (5, 10, 15, 125000)
Explanation
As you can see in the table above, here we have created a table of employee salary slab, where column ExperienceFrom and ExperienceTo indicates a range of work experience for the employee and column SalarySlab indicates salary of an employee based on the experience range.
Here, we will find the salary of the employee from the employee tbl_SalarySlabs table based on the total work experience of the employee.
Find employee Salary from salary slab table
DECLARE @TotalWorkExperience INT
SET @TotalWorkExperience = 4
SELECT ExperienceFrom, ExperienceTo, SalarySlab
FROM tbl_SalarySlabs
WHERE @TotalWorkExperience BETWEEN ExperienceFrom AND ExperienceTo
Explanation
As, you can see in the query above, here we have declared an integer variable @TotalWorkExperience and set integer value to a declared variable as the total experience of an employee.
We have selected ExperienceFrom, ExperienceTo, SalarySlab from tbl_SalarySlabs table and used BETWEEN operator in the where clause of the select statement and with the help of declared variable @TotalWorkExperience find the records that contain the value of variable @TotalWorkExperience in the range ExperienceFrom, ExperienceTo.
Output
Summary
In this article, we have learned how to find find the particular records from the given range in the SQL server using the BETWEEN operator.
