Ranges of values

You can use the BETWEEN predicate to select rows in which a column has a value within two limits.

You can specify the lower boundary of the BETWEEN predicate first, and then specify the upper boundary. The limits are inclusive.

Examples

Begin general-use programming interface information.

Example 1

Suppose that you specify the following WHERE clause in which the value of the column-name column is an integer:

WHERE column-name BETWEEN 6 AND 8

Db2 selects all rows whose column-name value is 6, 7, or 8. If you specify a range from a larger number to a smaller number (for example, BETWEEN 8 AND 6), the predicate never evaluates to true.

Example 2

This query retrieves the department number and manager number of each department whose number is between C00 and D31:

SELECT DEPTNO, MGRNO
  FROM DEPT
  WHERE DEPTNO BETWEEN 'C00' AND 'D31';

End general-use programming interface information.

You can also use NOT BETWEEN to select rows in which a column has a value that is outside the two limits.