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, this SELECT returns all products whose units in stock is between 15 and 25:
SELECT UnitsInStock, ProductID, ProductName
FROM Northwind.dbo.Products
WHERE UnitsInStock BETWEEN 15 AND 25
ORDER BY UnitsInStock
The results of this SELECT statement contains any products that have either 15 or 25 units in stock.
SELECT UnitsInStock, ProductID, ProductName
FROM Northwind.dbo.Products
WHERE UnitsInStock = 15 OR UnitsInStock = 25
ORDER BY UnitsInStock
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 UnitsInStock, ProductID, ProductName
FROM Northwind.dbo.Products
WHERE UnitsInStock > 15 AND UnitsInStock < 25
ORDER BY UnitsInStock
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 UnitsInStock, ProductID, ProductName
FROM Northwind.dbo.Products
WHERE UnitsInStock NOT BETWEEN 15 AND 25
ORDER BY UnitsInStock