Between Clause

This SQL Server tutorial explains how to use the BETWEEN condition in SQL Server (Transact-SQL) with syntax and examples.

Description

The SQL Server (Transact-SQL) BETWEEN condition is used to retrieve values within a range in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the BETWEEN condition in SQL Server (Transact-SQL) is:

expression BETWEEN value1 AND value2;

Parameters or Arguments

expression

A column or calculation.

value1 and value2

These values create an inclusive range that expression is compared to.

Note

• The SQL Server BETWEEN condition will return the records where expression is within the range of value1 and value2 (inclusive).

Example - With Numeric

Let's look at some SQL Server BETWEEN condition examples using numeric values. The following numeric example uses the BETWEEN condition to retrieve values within a numeric range.

For example:

SELECT *
FROM employees
WHERE employee_id BETWEEN 25 AND 100;

This SQL Server BETWEEN example would return all rows from the employees table where the employee_id is between 25 and 100 (inclusive). It is equivalent to the following SELECT statement:

SELECT *
FROM employees
WHERE employee_id >= 25
AND employee_id <= 100;

Example - With Date

Next, let's look at how you would use the SQL Server BETWEEN condition with Dates. The following date example uses the BETWEEN condition to retrieve values within a date range.

For example:

SELECT *
FROM employees
WHERE start_date BETWEEN '2014/05/01' AND '2014/05/31';

This SQL Server BETWEEN condition example would return all records from the employees table where the start_date is between May 1, 2014 and May 31, 2014 (inclusive). It would be equivalent to the following SELECT statement:

SELECT *
FROM employees
WHERE start_date >= '2014/05/01'
AND start_date <= '2014/05/31';

Example - Using NOT Operator

The SQL Server BETWEEN condition can also be combined with the SQL Server NOT operator. Here is an example of how you would combine the BETWEEN condition with the NOT Operator.

For example:

SELECT *
FROM employees
WHERE employee_id NOT BETWEEN 2000 AND 2999;

This SQL Server BETWEEN example would return all rows from the employees table where the employee_id was NOT between 2000 and 2999, inclusive. It would be equivalent to the following SELECT statement:

SELECT *
FROM employees
WHERE employee_id < 2000
OR employee_id > 2999;