Expansion of Between-predicate

The between-predicate tests whether a value is within a range of values.
idmscu19
The between-predicate tests whether a value is within a range of values.
Usage
Comparable Data Types
The data types of the values specified in a between-predicate must be comparable.
For information about comparing values of different data types, see Comparison, Assignment, Arithmetic, and Concatenation Operations.
Truth Value of a BETWEEN Predicate without NOT
The result of a BETWEEN predicate that does
not
include NOT is:
  • True when value being tested is greater than or equal to the starting value of the test range and less than or equal to the ending value of the test range
  • False when the value being tested is less than the starting value of the test range or greater than the ending value of the test range
  • Unknown when one or more of the values specified in the predicate are null
Truth Value of a BETWEEN predicate with NOT
The result of a BETWEEN predicate that includes NOT is:
  • True when value being tested is less than the starting value of the test range or greater than the ending value of the test range
  • False when the value being tested is greater than or equal to the starting value of the test range and less than or equal to the ending value of the test range
  • Unknown when one or more of the values specified in the predicate are null
Rounded Values
The BETWEEN predicate is useful for testing values derived from value expressions that may have been affected by rounding.
Example
As the Search Condition in a CHECK Parameter
The following CREATE TABLE statement defines the EXPERTISE table with four columns. The CHECK parameter defines a restriction on the data that can be stored in the SKILL_LEVEL column.
create table expertise    (emp_id      integer       not null,    skill_id     integer       not null,    skill_level  character(2),    exp_date     date,    check (skill_level between '01' and '04');
More Information