Learning Objective
The objective of this SQL Server tutorial is to teach you how to use the BETWEEN logical operator to evaluate whether a value is within a specified range.
What is BETWEEN operator in SQL Server?
The SQL Server BETWEEN operator is used to specify a numeric or date range and checks whether a value falls within the range. We have to specify a start and end value for the range and the BETWEEN condition evaluates to true if a value is greater than or equal to the start value and less than or equal to the end value. It can be used in SELECT, UPDATE or DLETE query and is an elegant alternative to <= and >= to specify a range.
Operation
The SQL Server BETWEEN operator follows the WHERE clause in a SQL query and returns those rows in the resultset where the value in the column or expression falls within the range defined using the BETWEEN operator. It is also possible to negate the effect of the BETWEEN operator by adding NOT (which makes it NOT BETWEEN) so that the query returns resultset where the value in the column or expression does not fall within the range defined by the BETWEEN operator.
SQL Server BETWEEN Syntax
The basic syntax of SQL Server BETWEEN operator is as follows.
SELECT expressions
FROM tables
[WHERE expression BETWEEN | NOT BETWEEN (start_value AND end_value)];
In this syntax,
- expressions – expressions defined here the column(s) or calculation you want to retrieve. If you want to retrieve all the columns simply use * in the place of expressions.
- tables – one or more than one table from where you want to retrieve data.
- WHERE conditions – Optional. This is used to specify some conditions while selecting data. In case you are not using WHERE clause all the rows available will be selected.
- BETWEEN – Logical operator used to positively evaluate a value against a numeric or date range.
- NOT BETWEEN – Logical operator used to negatively evaluate a value against a numeric or date range.
- AND– Used to concatenate multiple conditions.
SQL Server BETWEEN operator Examples
NOTE: Columns are also referred to as fields or attributes and the terms are used interchangeably.
Let us see how the BETWEEN operator is used in the different query types. Suppose we have a customer table as below. We will query the same to demonstrate the different usages of the BETWEEN operator.
| customer_id | first_name | last_name | customer_email | customer_since | total_purchase |
| 1 | Alicia | Keys | alicia_alicia@gmail.com | 1/10/2000 | 999.99 |
| 2 | Indi | Rossi | indi4u@gmail.com | 10/13/2010 | 777 |
| 3 | Jack | Smith | js2-k@hotmail.com | 11/7/2007 | 500.5 |
| 6 | Lauren | Crow | lcc2k02@gmail.com | 11/27/2017 | 200 |
| 7 | Stephen | Fleming | fire_stephen_01@gmail.com | 10/23/2019 | 207.55 |
| 8 | Vanessa | May | vanessamayU@hotmail.com | 5/25/2005 | 715 |
| 11 | Stacey | John | johnstaceyJSUS@hotmail.com | 8/15/2009 | 1001.01 |
| 12 | Rameses | Williams | WR04_04@yahoo.com | 7/4/2018 | 106.49 |
1) SQL Server BETWEEN – numeric range evaluation example
The below query is an example where the BETWEEN operator is used to retrieve the list where the total value of purchase by the customer is between the specified range.
SELECT *
FROM customers
WHERE total_purchase BETWEEN 500.00 AND 1000.00
ORDER BY total_purchase;
The query will generate the following output.
| customer_id | first_name | last_name | customer_email | customer_since | total_purchase |
| 3 | Jack | Smith | js2-k@hotmail.com | 11/7/2007 | 500.5 |
| 8 | Vanessa | May | vanessamayU@hotmail.com | 5/25/2005 | 715 |
| 2 | Indi | Rossi | indi4u@gmail.com | 10/13/2010 | 777 |
| 1 | Alicia | Keys | alicia_alicia@gmail.com | 1/10/2000 | 999.99 |
2) SQL Server BETWEEN – date range example
The below query is an example where the BETWEEN operator is used to retrieve the list of customers who registered within the specified time period.
SELECT *
FROM customers
WHERE customer_since BETWEEN '2005-01-01' AND '2015-01-01'
ORDER BY customer_since;
The query will generate the following output.
| customer_id | first_name | last_name | customer_email | customer_since | total_purchase |
| 8 | Vanessa | May | vanessamayU@hotmail.com | 5/25/2005 | 715 |
| 3 | Jack | Smith | js2-k@hotmail.com | 11/7/2007 | 500.5 |
| 11 | Stacey | John | johnstaceyJSUS@hotmail.com | 8/15/2009 | 1001.01 |
| 2 | Indi | Rossi | indi4u@gmail.com | 10/13/2010 | 777 |
3) SQL Server BETWEEN – NOT BETWEEN example
The below query is an example where the BETWEEN operator is used for negative matching with a numeric range to retrieve the list of customers whose total purchases value is not within the specified range (i.e. where the total purchase value is either less than or more than the range).
SELECT *
FROM customers
WHERE total_purchase NOT BETWEEN 500.00 AND 1000.00
ORDER BY total_purchase;
The query will generate the following output.
| customer_id | first_name | last_name | customer_email | customer_since | total_purchase |
| 12 | Rameses | Williams | WR04_04@yahoo.com | 7/4/2018 | 106.49 |
| 6 | Lauren | Crow | lcc2k02@gmail.com | 11/27/2017 | 200 |
| 7 | Stephen | Fleming | fire_stephen_01@gmail.com | 10/23/2019 | 207.55 |
4) SQL Server BETWEEN – UPDATE query example
The below query is an example of how the BETWEEN operator can be used in an update query to modify column values where the referenced column value fall within the range specified by BETWEEN. The referenced column can be the column which is being updated or a different column. In this example the referenced column and the updated column is the same i.e. total_purchase.
UPDATE customers
SET total_purchase=NULL
WHERE total_purchase BETWEEN 600.00 AND 900.00;
We can check the result of the update by running below SELECT query which will show the NULL values.
SELECT *
FROM customers
where total_purchase=NULL;
| customer_id | first_name | last_name | customer_email | customer_since | total_purchase |
| 2 | Indi | Rossi | indi4u@gmail.com | 10/13/2010 | NULL |
| 8 | Vanessa | May | vanessamayU@hotmail.com | 5/25/2005 | NULL |
5) SQL Server BETWEEN – DELETE query example
The below query is an example of how the BETWEEN operator can be used to delete rows from a table if the BETWEEN condition specified on a column (i.e. referenced column) is satisfied.
DELETE
FROM customers
WHERE customer_since BETWEEN '2005-01-01' AND '2015-01-01';
We can check the result of the DELETE operation by running a SELECT query.
SELECT * FROM customers;
It will generate the following output. We can see in the output that only 4 rows remain after the deletion.
| customer_id | first_name | last_name | customer_email | customer_since | total_purchase |
| 1 | Alicia | Keys | alicia_alicia@gmail.com | 1/10/2000 | 999.99 |
| 6 | Lauren | Crow | lcc2k02@gmail.com | 11/27/2017 | 200 |
| 7 | Stephen | Fleming | fire_stephen_01@gmail.com | 10/23/2019 | 207.55 |
| 12 | Rameses | Williams | WR04_04@yahoo.com | 7/4/2018 | 106.49 |