Suppose we want to see all friends with ages between 22 and 25. Figure
shows two queries that produce this result.
test=> SELECT *
test-> FROM friend
test-> WHERE age >= 22 AND age <= 25
test-> ORDER BY firstname;
firstname | lastname | city | state | age
-----------------+----------------------+-----------------+-------+-----
Dean | Yeager | Plymouth | MA | 24
Sandy | Gleason | Ocean City | NJ | 25
Victor | Tabor | Williamsport | PA | 22
(3 rows)
test=> SELECT *
test-> FROM friend
test-> WHERE age BETWEEN 22 AND 25
test-> ORDER BY firstname;
firstname | lastname | city | state | age
-----------------+----------------------+-----------------+-------+-----
Dean | Yeager | Plymouth | MA | 24
Sandy | Gleason | Ocean City | NJ | 25
Victor | Tabor | Williamsport | PA | 22
(3 rows)
The first query uses AND to perform two comparisons that both must be true. We used <= and >= so the age comparisons included the limiting ages of 22 and 25. If we used < and >, the ages 22 and 25 would not have been included in the output. The second query uses BETWEEN to generate the same comparison. BETWEEN comparisons include the limiting values in the result.