BETWEEN

Go Up to Using Comparison Operators in Expressions

BETWEEN tests whether a value falls within a range of values. The complete syntax for the BETWEEN operator is:

<value> [NOT] BETWEEN <value> AND <value>

For example, the following cursor declaration retrieves LAST_NAME and FIRST_NAME columns for employees with salaries between $100,000 and $250,000, inclusive:

EXEC SQL
DECLARE LARGE_SALARIES CURSOR FOR
SELECT LAST_NAME, FIRST_NAME
FROM EMPLOYEE
WHERE SALARY BETWEEN 100000 AND 250000;

Use NOT BETWEEN to test whether a value falls outside a range of values. For example, the following cursor declaration retrieves the names of employees with salaries less than $30,000 and greater than $150,000:

EXEC SQL
DECLARE EXTREME_SALARIES CURSOR FOR
SELECT LAST_NAME, FIRST_NAME
FROM EMPLOYEE
WHERE SALARY NOT BETWEEN 30000 AND 150000;

Advance To:

• Using CONTAINING