Practical SQL Handbook, The: Using SQL Variants, Fourth Edition

Ranges (BETWEEN and NOT BETWEEN)

Another common search condition is a range. There are two different ways to specify ranges:

• With the comparison operators > and <

• With the keyword BETWEEN

Use BETWEEN to specify an inclusive range, in which you search for the lower value and the upper value as well as the values they bracket. For example, to find all the books with sales between (and including) 4,095 and 12,000, you could write this query:

SQL

select title_id, ytd_sales
from titles
where ytd_sales between 4095 and 12000
title_id   ytd_sales
======== ===========
PC8888          4095
BU1032          4095
TC7777          4095
PC1035          8780
BU7832          4095
[5 rows]

Notice that books with sales of 4,095 are included in the results. If there were any with sales of 12,000, they would ...