Boolean Logic in SQL

This chapter is from the book

The Language of SQL, 3rd Edition

Learn More Buy

This chapter is from the book

This chapter is from the book 

The Language of SQL, 3rd Edition

Learn More Buy

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:

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: