Expansion of Between-predicate

Predicates and Search Condition › Expansion of Between-predicate

More information:

ALTER TABLE

CREATE TABLE

Expansion of Search-condition

Expansion of Between-predicate

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.

Note: 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

For more information about search conditions, see Expansion of Search-condition.
For more information about the CHECK parameter, see CREATE TABLE.
For more information about the ADD CHECK parameter, see ALTER TABLE.