DB2 IN, BETWEEN, LIKE, NULL and DISTINCT predicates

Map of the predicates

All of them yield TRUE, FALSE, or UNKNOWN except IS NULL / IS NOT NULL and the DISTINCT predicate, which are defined so that nulls are handled and the result is TRUE or FALSE. WHERE still keeps TRUE only.

BETWEEN and NOT BETWEEN

COL BETWEEN expr1 AND expr2 is COL >= expr1 AND COL <= expr2. Both ends are included. NOT BETWEEN is NOT (BETWEEN): outside the closed range. If expr1 > expr2, BETWEEN is FALSE for ordinary values (Db2 does not swap the ends). If any of COL, expr1, or expr2 is null, BETWEEN is UNKNOWN.

1
2
3
4
SELECT EMPNO, SALARY
  FROM DSN8C10.EMP
 WHERE SALARY BETWEEN 20000 AND 40000
   AND HIREDATE NOT BETWEEN '1980-01-01' AND '1989-12-31';

Character BETWEEN uses string comparison (trailing blanks not significant). Datetime BETWEEN uses chronological order. For TIMESTAMP “all of 2000-01-15,” write TS >= '2000-01-15-00.00.00' AND TS < '2000-01-16-00.00.00' so you do not miss or double-count fractional seconds at midnight.

BETWEEN on a leading indexed column is a range matching predicate. NOT BETWEEN is typically not matching. Two endpoints that are expressions (YEAR(CURRENT DATE)) can still be stage 1 if the column stands alone.

IN and NOT IN

IN tests membership. Two forms:

• List — WORKDEPT IN ('A00', 'B01', 'D11') equivalent to a chain of OR equalities
• Subquery — EMPNO IN (SELECT EMPNO FROM DSN8C10.PROJACT) equivalent to = ANY (fullselect)

1
2
3
4
SELECT LASTNAME
  FROM DSN8C10.EMP
 WHERE WORKDEPT IN ('A00', 'B01')
   AND EMPNO NOT IN (SELECT EMPNO FROM DSN8C10.PROJACT);

Row-value IN is allowed: (COL1, COL2) IN (SELECT C1, C2 FROM T). Degree must match. Subqueries in IN should not return duplicate-heavy sets for performance; DISTINCT in the subquery or a semi-join EXISTS rewrite can help the optimizer.

The NOT IN null trap

NOT IN (fullselect) is <> ALL. If the subquery returns a null, then for a candidate value V that is not equal to any non-null member, V <> NULL is UNKNOWN, so ALL is not TRUE. The outer row vanishes even though V “is not in the real list.” Lists can trap you the same way: COL NOT IN (1, 2, NULL) never yields TRUE.

Fix: make the subquery non-null (WHERE col IS NOT NULL), or rewrite as NOT EXISTS (SELECT 1 FROM t WHERE t.col = outer.col). EXISTS does not go UNKNOWN from a null equality inside in the same way—the correlated equality simply fails to match nulls, and a lack of matching rows makes NOT EXISTS TRUE.

LIKE, NOT LIKE and ESCAPE

LIKE matches a string against a pattern. % is any length (including empty). _ is exactly one character. NOT LIKE is NOT (LIKE). If the string or pattern is null, LIKE is UNKNOWN. Two empty strings LIKE each other (TRUE).

1
2
3
4
SELECT LASTNAME
  FROM DSN8C10.EMP
 WHERE LASTNAME LIKE 'S%'
    OR LASTNAME LIKE 'HA_S';

ESCAPE

When the data contains % or _, ESCAPE names a prefix character that turns the next wildcard into a literal.

1
2
3
4
-- Rows where CODE contains a literal percent
SELECT CODE
  FROM T
 WHERE CODE LIKE '%@%%' ESCAPE '@';

Pattern %@%% with ESCAPE @ means: any prefix, a literal %, any suffix. Double the escape to include the escape character itself. ESCAPE is not valid for mixed EBCDIC/ASCII mixed data; Unicode mixed is allowed. Binary LIKE uses a 1-byte escape.

LIKE 'ABC%' can use an index on the column (matching prefix). LIKE '%ABC' cannot start an index probe. Underscore in the first position is also not a matching prefix. UPPER(LASTNAME) LIKE 'SMI%' usually disables matching unless LASTNAME is always stored upper or you index an expression/generated column.

IS NULL and IS NOT NULL

COL IS NULL is TRUE when the column is the null value, FALSE otherwise. It is never UNKNOWN. COL IS NOT NULL is the opposite. These are the only honest null tests.

1
2
3
SELECT EMPNO, COMM
  FROM DSN8C10.EMP
 WHERE COMM IS NULL;

In COBOL, a null indicator of −1 is the host-side IS NULL. Comparing the host variable without checking the indicator reintroduces the = NULL mistake. IS NULL on a leading indexed nullable column can be indexable (IBM lists COL IS NULL among predicates that can be matching in documented cases). IS NOT NULL is often used to skip residual null handling in joins and aggregations.

DISTINCT predicate: IS DISTINCT FROM

The DISTINCT predicate is the null-safe comparison:

• COL1 IS NOT DISTINCT FROM COL2 — TRUE when both are the same non-null value, or both are null. This is null-safe equals.
• COL1 IS DISTINCT FROM COL2 — TRUE when they differ as values, or when one is null and the other is not. Two nulls are not distinct, so this is FALSE when both are null.

1
2
3
SELECT *
  FROM T1 INNER JOIN T2
    ON T1.C1 IS NOT DISTINCT FROM T2.C2;

That join matches null keys to null keys, unlike ON T1.C1 = T2.C2. Use it when null means “the same unknown business key” (rare—usually null means “no key” and you do not want them to match). IS NOT DISTINCT FROM value can be matching; IS DISTINCT FROM value is typically stage 1 not matching, similar to <>.

Do not confuse this with SELECT DISTINCT, UNIQUE constraints, or DISTINCT in COUNT. Those remove or forbid duplicate rows. This predicate compares two expressions.

Choosing among them

• Closed numeric or date range — BETWEEN (or >= and <=)
• A handful of codes — IN list; many codes — table of codes and join/IN subquery
• String prefix search — LIKE 'x%'
• Missing value — IS NULL, never = NULL
• Compare including both-null — IS NOT DISTINCT FROM
• Anti-membership on a nullable subquery column — NOT EXISTS, not NOT IN

Explain It Like I'm Five

BETWEEN is “are you on the number line between these two stickers, touching stickers allowed?” IN is “is your name on this party list?” LIKE is “does your name match this stencil?” where % is a blob of any letters and _ is one mystery letter. ESCAPE is a magic marker that says “the next stencil hole is just paint, not a mystery.” IS NULL is “is the magnet missing?” DISTINCT FROM is a special same/different game where two missing magnets count as the same kind of missing, unlike the ordinary = game where missing never equals missing.

Exercises

1. Write BETWEEN and the equivalent pair of comparisons for SALARY from 25000 to 30000. Confirm they return the same rows.
2. Build a NOT IN subquery that includes a nullable column, then rewrite it as NOT EXISTS. Show a row that appears only in the EXISTS form.
3. Write LIKE with ESCAPE to find values that contain an underscore in the second character.
4. Contrast WHERE PHONENO = :HV with WHERE PHONENO IS NOT DISTINCT FROM :HV when the indicator is −1.
5. Explain why LIKE '%SON' on LASTNAME is likely to scan more than LIKE 'S%'.