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
This SQL Server BETWEEN example would retrieve all employees whose first name is in the letter range between A and G (alphabetically, as in a telephone book):
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