SQL Server BETWEEN Operator

This SQL tutorial focuses on the SQL Server BETWEEN operator, and provides explanations, examples and exercises. For this lesson’s exercises, use this link.

This tutorial is a part of several posts explaining how to use the WHERE clause in SQL Server. To read additional posts regarding this subject, please use the following links:

• Introduction – SQL Server WHERE clause
• Comparison operators
• IN operator
• BETWEEN operator
• LIKE operator
• IS NULL operator
• AND & OR operators

SQL Server BETWEEN Operator

The SQL Server between operator is used to retrieve values based on a certain range.

WHERE column_name BETWEEN  lower_value AND upper_value

BETWEEN Operator with Numeric Values

This SQL Server BETWEEN example would retrieve all employees whose salary is in the range between 5000 and 8000:

SELECT last_name , salary
FROM employees
WHERE  salary BETWEEN 5000 AND 8000

BETWEEN Operator with Character Values

SELECT first_name
FROM employees
WHERE  first_name BETWEEN 'A' AND 'G'

• The string values must be enclosed within single quotes (‘string’)
• In SQL Server string values are not case-sensitive by default. The actual value in the column does not have to match the value you specify in the BETWEEN condition. For example, if you specify the letter range A-G and there is an employee whose first name is “antony” (first letter is in lowercase), the name of this employee will be retrieved.

BETWEEN Operator with Date Values

 :This SQL Server BETWEEN example would retrieve all employees whose date of employment is between 01.JAN.1990 and 01.JAN.2000

SELECT first_name, hire_date
FROM employees
WHERE hire_date BETWEEN '1990-01-01' AND '2000-01-01'

• Date values must be enclosed within single quotes (‘date’)
• Date values are format sensitive.

SQL Server NOT Operator

This operator is used to produce opposite results than those produced by the BETWEEN operator.

The NOT keyword must be written before the BETWEEN keyword.

This SQL Server BETWEEN example would retrieve all employees whose salary is in the range between 5000 and 8000:

SELECT *
FROM employees
WHERE  salary BETWEEN 5000 AND 8000

This SQL Server BETWEEN example would retrieve all employees whose salary is not in the range between 5000 and 8000:

SELECT *
FROM employees
WHERE  salary NOT BETWEEN 5000 AND 8000

General Guidelines

In a SQL Server BETWEEN operator, the lower limit must be specified before the upper limit.

SELECT *
FROM employees
WHERE  salary BETWEEN 8000 AND 2000
-- No error will be generated and no rows will be returned either.

The SQL Server BETWEEN operator is inclusive. For example, when you use the SQL Server BETWEEN operator to retrieve the employees whose salary is in the range between 5000 and 9000, the result retrieves all of these employees, including those whose salary equals 5000 or 9000.

SELECT *
FROM employees
WHERE  salary BETWEEN 5000 AND 9000
-- All employees whose salary is in the range of 5000 and 9000
-- Including those whose salary equals 5000 or 9000