SQL Server Range Operators

SQL Server Range Operators

Range operators are used to retrieve the data using the condition specified in ranges.

SQL Server supports range operators such as BETWEEN and NOT BETWEEN.

• BETWEEN : specifies condition in inclusive ranges to search the rows in database table.
• NOT BETWEEN : specifies condition in exclusive ranges to search the rows in database table.

Range operators can be used in the WHERE clause.

Range Operators Syntax

SELECT column_name1[, column_name2, ....]
FROM table_name
WHERE expression1 [BETWEEN | NOT BETWEEN ] expression2 AND expression3 

Employee Table

Let us consider the example 'Employee' database table.

BETWEEN Operator on WHERE Statement

BETWEEN operator is used to specify the condition based on ranges in WHERE clasue.

BETWEEN Operator Example

To get rows based on salary range from 6000 to 8000 in a Employee table.

SELECT Name, DeptId, City, Email, Salary
FROM Employee
WHERE Salary BETWEEN 6000 AND 8000

To get rows based on DeptId in the range from 2 to 6.

SELECT Name, DeptId, City, Email, Salary
FROM Employee
WHERE DeptId BETWEEN 2 AND 6

NOT BETWEEN Operator on WHERE Statement

NOT BETWEEN operator is used to specify the negative condition based on ranges in WHERE clasue.

NOT BETWEEN Operator Example

To get rows based on salary other than in the range from 6000 to 8000 in a Employee table.

SELECT Name, DeptId, City, Email, Salary
FROM Employee
WHERE Salary NOT BETWEEN 6000 AND 8000

To get rows based on DeptId other than in the range from 2 to 6.

SELECT Name, DeptId, City, Email, Salary
FROM Employee
WHERE DeptId NOT BETWEEN 2 AND 6