Range Search Conditions

A range search is one that returns all values between two specified values. Inclusive ranges return any values that match the two specified values. Exclusive ranges do not return any values that match the two specified values.

The BETWEEN keyword specifies an inclusive range to search. For example, the following SELECT returns all products whose list price is between $15 and $25:

USE AdventureWorks;
GO
SELECT ProductID, Name
FROM AdventureWorks.Production.Product 
WHERE ListPrice BETWEEN 15 AND 25

The results of the following SELECT statement contain any whose list price is either $15 or $25.

SELECT ProductID, Name
FROM AdventureWorks.Production.Product 
WHERE ListPrice = 15 OR ListPrice = 25

To specify an exclusive range, use the greater-than and less-than operators (> and <). The following query using the greater-than and less-than operators returns different results than the last example because these operators do not include rows matching the values that limit the range.

SELECT ProductID, Name
FROM AdventureWorks.Production.Product 
WHERE ListPrice > 15 AND ListPrice < 25

NOT BETWEEN finds all rows outside the range you specify. Use this query to find all products for which the number of units in stock are outside the 15 to 25 range:

SELECT ProductID, Name
FROM AdventureWorks.Production.Product 
WHERE ListPrice NOT BETWEEN 15 AND 25