The BETWEEN operator is used in a WHERE clause to filter values within a range.

Basic syntax:

SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
  • BETWEEN is inclusive: it includes both value1 and value2.
  • Can be used with numbers, dates, or text.

Step 1: Example table

Assume the Employees table:

EmployeeID FirstName LastName Department Salary
1 John Doe IT 5000
2 Jane Smith HR 4500
3 Mike Brown IT 6000
4 Sara White HR 4700
5 Tom Green IT 5200

Step 2: Using BETWEEN with numbers

Example: Employees with salary between 4500 and 5200

SELECT FirstName, Salary
FROM Employees
WHERE Salary BETWEEN 4500 AND 5200;

Result:

FirstName Salary
John 5000
Jane 4500
Sara 4700
Tom 5200

Both 4500 and 5200 are included.

Step 3: Using BETWEEN with dates

Assume we have a JoiningDate column:

EmployeeID FirstName JoiningDate
1 John 2023-01-10
2 Jane 2023-03-15
3 Mike 2023-02-20

Example: Employees who joined between 2023-01-01 and 2023-02-28

SELECT FirstName, JoiningDate
FROM Employees
WHERE JoiningDate BETWEEN '2023-01-01' AND '2023-02-28';

Result:

FirstName JoiningDate
John 2023-01-10
Mike 2023-02-20

Step 4: Using NOT BETWEEN

NOT BETWEEN returns rows outside the specified range.

SELECT FirstName, Salary
FROM Employees
WHERE Salary NOT BETWEEN 4500 AND 5200;

Result:

FirstName Salary
Mike 6000

Only Mike’s salary is outside the range.

Step 5: Important Notes

  • BETWEEN is inclusive (includes boundary values).
  • Can be used with numeric, date, or text values.
  • Always use NOT BETWEEN if you want the opposite range.

Categorized in:

SQL Server,