How to Find Values Within a Given Range in SQL server

watch_later Sunday, February 9, 2020

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.


Find Values Within a Given Range 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_name
WHERE 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 

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.

Codingvila provides articles and blogs on web and software development for beginners as well as free Academic projects for final year students in Asp.Net, MVC, C#, Vb.Net, SQL Server, Angular Js, Android, PHP, Java, Python, Desktop Software Application and etc.



sentiment_satisfied Emoticon