How to Find Values Within a Given Range in SQL server

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.

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.