Module 5: DB2 SQL SELECT and Filtering

BETWEEN

BETWEEN selects values inside a range - salaries between two amounts, hire dates within a decade. It is the range counterpart of IN.

BETWEEN syntax and meaning

Syntax: WHERE column BETWEEN low AND high.
Both boundaries are inclusive: BETWEEN 30000 AND 40000 keeps 30000 and 40000.
It is shorthand for column >= low AND column <= high.
NOT BETWEEN reverses it: values outside the range (boundaries excluded).

BETWEEN with numbers, dates and characters

Numbers: BETWEEN 30000 AND 40000.
Dates: BETWEEN '1970-01-01' AND '1979-12-31' - use ISO format.
Characters: BETWEEN 'A' AND 'C' uses EBCDIC ordering on the mainframe.
The low value must come first - BETWEEN 40000 AND 30000 matches nothing.

When not to use BETWEEN

For open-ended ranges use > or < directly - they read clearer.
For discrete hand-picked values use IN instead.
BETWEEN on timestamps includes the whole end day only if you write the time: '1979-12-31-23.59.59.999999'.
Watch decimal scales: BETWEEN 10.5 AND 20 keeps 10.50 as well - same value.

Example

SELECT EMPNO, LASTNAME, SALARY FROM EMP WHERE SALARY BETWEEN 30000 AND 40000; -- Inclusive: 30000 and 40000 themselves would match too EMPNO LASTNAME SALARY ------ -------- ------ 000030 KWAN 38250 000060 STERN 32250 000070 PULASKI 36170

Backward