This chapter is from the book
This chapter is from the book
This chapter is from the book
The BETWEEN Operator
Now let’s turn to two special operators, BETWEEN and IN, that can simplify expressions that would ordinarily require the OR or AND operators. The BETWEEN operator allows us to abbreviate an AND expression with greater than or equal to (>=) and less than or equal to (<=) operators in an expression with a single operator.
Let’s say, for example, that we want to select all rows with a quantity purchased from 5 to 20. One way of accomplishing this is with the following SELECT statement:
SELECT CustomerName, QuantityPurchased FROM Purchases WHERE QuantityPurchased >= 5 AND QuantityPurchased <= 20
Using the BETWEEN operator, the equivalent statement is:
SELECT CustomerName, QuantityPurchased FROM Purchases WHERE QuantityPurchased BETWEEN 5 AND 20
In both cases, the SELECT returns this data:
CustomerName |
QuantityPurchased |
Sandy Harris |
10 |
James Turban |
5 |
The BETWEEN operator always requires a corresponding AND placed between the two numbers.
Note the relative simplicity of the BETWEEN operator. Also notice that the BETWEEN is inclusive of the numbers specified. In this example, BETWEEN 5 AND 20 includes the numbers 5 and 20. Thus, the BETWEEN is equivalent only to the greater than or equal to (>=) and less than or equal to (<=) operators. It can’t be used to express something simply greater than (>) or less than (<) a range of numbers. The row for James Turban is selected because the quantity purchased is equal to 5, and therefore is between 5 and 20.
The NOT operator can be used in conjunction with BETWEEN. For example, this SELECT:
SELECT CustomerName, QuantityPurchased FROM Purchases WHERE QuantityPurchased NOT BETWEEN 5 AND 20
retrieves this data:
CustomerName |
QuantityPurchased |
Kim Chiang |
4 |