SQL in a Nutshell, 2nd Edition

Name

BETWEEN Operator

Synopsis

The BETWEEN operator performs a Boolean test of a value against a range of values. It returns TRUE when the value is included in the range and FALSE when the value falls outside of the range. The results are NULL (unknown) if any of the range values are NULL.

Platform	Command
DB2	Supported
MySQL	Supported
Oracle	Supported
PostgreSQL	Supported
SQL Server	Supported

SQL2003 Syntax

SELECT ...
WHERE expression [NOT] BETWEEN lower_range ANDupper_range

Keywords

WHERE expression

Compares a scalar expression, such as a column, to the range of values bounded by upper_range and lower_range.

[NOT] BETWEEN lower_range AND upper_range

Compares the expression to the lower_range and upper_range. The comparison is inclusive, meaning that it is equivalent to saying “where expression is [not] greater than or equal to lower_range and less than or equal to upper_range.”

Rules at a Glance

The BETWEEN operator is used to test an expression against a range of values. The BETWEEN operator may be used with any datatype except BLOB, CLOB, NCLOB, REF, and ARRAY.

For example, we want to see title_ids that have year-to-date sales between 10,000 and 20,000:

SELECT title_id
FROM titles
WHERE ytd_sales BETWEEN 10000 AND 20000

BETWEEN is inclusive of the range of values listed. In this case, it includes the values of 10,000 and 20,000 in the search. If you want an exclusive search, then you must use the greater than (>) and less than (<) symbols:

SELECT title_id FROM titles WHERE ...