BETWEEN and NOT BETWEEN in DB2 SQL

Inclusive ranges

The SQL Reference says the BETWEEN predicate determines whether a given value lies between two other given values that are specified in ascending order. The two forms have equivalent search conditions:

• value1 BETWEEN value2 AND value3 means value1 >= value2 AND value1 <= value3
• value1 NOT BETWEEN value2 AND value3 means value1 < value2 OR value1 > value3, or equivalently NOT (value1 BETWEEN value2 AND value3)

IBM notes those equivalences might not hold if the three operands are columns or derived values based on columns that are not the same CCSID set, because the clause can be evaluated in Unicode. For ordinary same-type numeric, date, or single-CCSID character columns, BETWEEN is the closed interval you expect: both endpoints match.

1
2
3
SELECT EMPNO, LASTNAME, SALARY
  FROM DSN8C10.EMP
 WHERE SALARY BETWEEN 30000 AND 50000;

An employee earning exactly 30000 or exactly 50000 is included. That is the whole point of BETWEEN versus a pair of strict inequalities. If your business rule is “up to but not including 50000,” do not use BETWEEN; write SALARY >= 30000 AND SALARY < 50000.

Bounds are not swapped. SALARY BETWEEN 50000 AND 30000 means salary >= 50000 AND salary <= 30000, which is empty for real salaries. If host variables can arrive reversed, swap them in the program or use CASE / MIN and MAX expressions—do not assume Db2 sorts the two numbers for you.

NOT BETWEEN

NOT BETWEEN keeps values strictly outside the closed interval:

1
2
3
SELECT EMPNO, SALARY
  FROM DSN8C10.EMP
 WHERE SALARY NOT BETWEEN 30000 AND 50000;

29999.99 and 50000.01 qualify; 30000 and 50000 do not. Null salaries do not qualify (UNKNOWN). NOT BETWEEN is often harder for the optimizer than BETWEEN (predicate tables list COL NOT BETWEEN value1 AND value2 as stage 1 not indexable in typical cases). If you need “outside this band” and performance matters, compare EXPLAIN of NOT BETWEEN versus two range predicates combined with OR.

Nulls and the IBM truth table

IBM documents A BETWEEN B AND C for various A, B, and C. Memorize the pattern; do not guess.

Takeaways:

• Null test value — always UNKNOWN, regardless of bounds.
• Null bound with a value clearly outside the remaining inequality — can be FALSE (0 is not >= 1 even if the high bound is null).
• Null bound when the other inequality could still hold — UNKNOWN.

If you need “salary in range, treating null as not in range,” BETWEEN already does that for WHERE. If you need nulls included, add OR SALARY IS NULL explicitly.

Date ranges

BETWEEN is the usual way to filter a DATE column for a closed calendar interval. Both the first and last day are included.

1
2
3
SELECT EMPNO, LASTNAME, HIREDATE
  FROM DSN8C10.EMP
 WHERE HIREDATE BETWEEN DATE('2020-01-01') AND DATE('2020-12-31');

If the operands mix datetime values and valid string representations of datetime values, Db2 converts all values to the data type of the datetime operand. Prefer typed DATE literals or DATE() constructors over ambiguous local strings like '01/31/2020' unless your session date format is guaranteed.

Timestamp columns and the last-day bug

If the column is TIMESTAMP (or TIMESTAMP WITH TIME ZONE) and you write:

1
2
-- Dangerous if CHANGED_TS is a timestamp
WHERE CHANGED_TS BETWEEN DATE('2024-01-01') AND DATE('2024-12-31')

The high bound DATE '2024-12-31' as a timestamp is 2024-12-31-00.00.00, not the last microsecond of New Year’s Eve. A row changed at 2024-12-31-14.00.00 is greater than that midnight and is excluded. The report “looks” like a full year and quietly drops most of the last day.

Safer patterns:

1
2
3
4
5
6
7
-- Half-open interval: include all of 2024
WHERE CHANGED_TS >= TIMESTAMP('2024-01-01-00.00.00')
  AND CHANGED_TS <  TIMESTAMP('2025-01-01-00.00.00')
 
-- Closed interval with an explicit last moment (precision must match the column)
WHERE CHANGED_TS BETWEEN TIMESTAMP('2024-01-01-00.00.00.000000')
                     AND TIMESTAMP('2024-12-31-23.59.59.999999')

Half-open ranges (>= start AND < next period) compose cleanly for months and days and avoid fighting timestamp precision. BETWEEN remains perfect for DATE columns where a day has no time component.

Do not wrap the column

YEAR(HIREDATE) = 2020 or DATE(CHANGED_TS) BETWEEN … applies a function to the column and often prevents a matching index. Write a range on the raw column:

1
2
WHERE HIREDATE BETWEEN DATE('2020-01-01') AND DATE('2020-12-31')
-- not: WHERE YEAR(HIREDATE) = 2020

Character and other types

Character BETWEEN uses collating sequence and CCSID conversion rules. For codes stored as CHAR(3), BETWEEN 'A00' AND 'A99' is a prefix-style range only if your encoding sorts those characters the way the business expects (EBCDIC letter/digit order is not ASCII). Test with real values; do not assume telephone-book order.

If any BETWEEN operand is numeric and another is a character or graphic string, Db2 can implicitly cast the string to DECFLOAT for the comparison (numeric is dominant). That surprises people who meant a string range. Keep types consistent: do not mix SALARY (decimal) with a character host variable without an explicit CAST.

BETWEEN of three columns (COL BETWEEN COL1 AND COL2) is a different access-path story than BETWEEN two constants. The manuals list column-to-column BETWEEN as stage 2 in common cases. When both bounds are literals or host variables, you give the optimizer a clear range.

BETWEEN versus IN versus LIKE

• BETWEEN — continuous range, inclusive ends (numbers, dates, ordered codes).
• IN — discrete membership (a set of departments, not a span).
• LIKE — character pattern, not a numeric interval.

A00 through A09 might be BETWEEN on a well-designed CHAR code, or IN if the set is sparse. Do not write LIKE 'A0%' to mean a numeric range of codes unless you have proven the pattern cannot match A0A or similar.

Explain It Like I'm Five

BETWEEN is asking whether your height is on the sticker that says “at least this tall AND not taller than this other line.” Both lines count: if you are exactly as tall as the top line, you still ride. NOT BETWEEN is “shorter than the short line or taller than the tall line.” If someone forgot to write a number on one sticker (NULL), the ride operator cannot say yes, so you do not ride. For birthday parties (dates), BETWEEN on a calendar date includes the first party day and the last party day. If the invitation used clock times, “until December 31” must mean the whole day, not midnight at the start of December 31.

Exercises

1. Write BETWEEN for SALARY from 40000 through 80000 inclusive, then the equivalent AND of comparisons.
2. Write NOT BETWEEN for HIREDATE outside 2019, using DATE literals.
3. Using IBM’s table, state the result of NULL BETWEEN 1 AND 3 and of 2 BETWEEN 1 AND NULL.
4. A TIMESTAMP column must include all of March 2024. Write a half-open range that does not drop March 31 evenings.
5. Explain why YEAR(HIREDATE) BETWEEN 2018 AND 2020 is usually a worse access path than a HIREDATE BETWEEN two dates.

Frequently asked questions

Can the bounds be expressions?

Yes. CURRENT DATE - 30 DAYS, host variables, and scalar functions are all expressions. Index matching is best when the column is bare and the bounds are constants or host variables.

Does BETWEEN work in CHECK constraints?

Search conditions in CHECK can use BETWEEN. The same inclusive and null rules apply. A CHECK that uses BETWEEN does not replace a business-date calendar table if holidays matter.

What about BETWEEN SYMMETRIC (SQL standard)?

Db2 for z/OS BETWEEN does not reorder bounds for you. There is no SYMMETRIC keyword in the z/OS BETWEEN predicate. If inputs might be reversed, order them in the application.