DB2 comparison predicates in depth: equal, not equal, greater and less

The six operators

1
2
3
4
5
SELECT EMPNO, LASTNAME, SALARY
  FROM DSN8C10.EMP
 WHERE SALARY >= 50000
   AND SALARY <> 52750
   AND WORKDEPT = 'A00';

Operands must be compatible types (or implicitly convertible). You cannot compare a BLOB to an INTEGER. Distinct types compare to themselves; comparing a distinct type to its source type usually needs CAST. Binary strings compare as bytes; character strings follow CCSID conversion then the collating rules for that encoding.

Equal

= is identity of values, not identity of rows. WORKDEPT = 'A00' is TRUE when the department code is exactly that string under character comparison rules. It is the predicate you want on join keys and on indexed lookup columns. Host variables should match the column type so Db2 does not add a cast that turns a matching predicate into a stage 2 residual.

For numbers, 5 = 5.0 is TRUE after numeric comparison (DECIMAL versus INTEGER conversion). For floats, equality is brittle: DOUBLE calculations that “look like” 0.1 may not equal DECIMAL 0.1. Compare money as DECIMAL.

Not equal

<> is TRUE when the values differ and both are non-null. Write <> in new SQL. !=, ^=, and ¬= are compatibility spellings; ¬ in particular is a code-page hazard when source moves between EBCDIC and ASCII tools.

NOT (COL = 5) is the same three-valued result as COL <> 5: if COL is null, the inner = is UNKNOWN, NOT UNKNOWN is still UNKNOWN. You do not get the null rows. To include nulls you must say COL <> 5 OR COL IS NULL (or use IS DISTINCT FROM).

Greater than, less than, and the inclusive forms

Order is type-specific:

• Numbers — algebraic order. −1 < 0 < 1. DECFLOAT NaN comparisons are special; do not assume NaN is greater than everything.
• Character — collating sequence after any needed conversion. Trailing blanks are not significant in the usual comparison, so CHAR pads do not make 'A' greater than VARCHAR 'A'. Leading blanks are significant. Mixed EBCDIC/Unicode comparisons convert first; when conversion is not possible, you get an error, not a mystery order.
• Graphic — graphic collating rules / UTF-16 code units depending on CCSID.
• DATE / TIME / TIMESTAMP — chronological. TIMESTAMP precision participates: a later fraction is greater. TIMESTAMP WITH TIME ZONE compares instants (normalized), not the display offset alone.
• ROWID — implementation-defined internal order; treat equality as the useful operation.

<= and >= include equality. A closed range is COL >= :lo AND COL <= :hi, which is what BETWEEN means. An open range uses < or > on one side (for example timestamps: TS >= :day AND TS < :day + 1 DAY).

NULL and UNKNOWN

Truth table for COL = 5 when COL may be null:

• COL is 5 — TRUE
• COL is 4 — FALSE
• COL is null — UNKNOWN

WHERE and HAVING keep TRUE only. CHECK constraints treat UNKNOWN as passing (the row is allowed) unless you write IS NOT NULL. JOIN ON is also a search condition: ON T1.K = T2.K does not match null keys to each other. That is why outer joins produce nulls on the non-preserved side instead of “null equals null” matches.

1
2
3
4
5
6
-- Does not return rows where COMM is null
SELECT EMPNO FROM DSN8C10.EMP WHERE COMM < 1000;
 
-- Null-safe “different from 1000, including missing commission”
SELECT EMPNO FROM DSN8C10.EMP
 WHERE COMM IS DISTINCT FROM 1000;

Row-value comparisons

(HIREDATE, EMPNO) > (:d, :e) compares like a composite sort key: HIREDATE first, then EMPNO when dates tie. Equality is pairwise. This form is useful for keyset pagination (“next page after this bookmark”) when a single column is not unique.

1
2
3
4
5
SELECT EMPNO, LASTNAME, HIREDATE
  FROM DSN8C10.EMP
 WHERE (HIREDATE, EMPNO) > (:LAST_DATE, :LAST_EMPNO)
 ORDER BY HIREDATE, EMPNO
 FETCH FIRST 20 ROWS ONLY;

A subquery on the other side of a basic predicate must be scalar (one column, at most one row) unless you are using a row-value versus a fullselect that returns one row of matching degree. Multiple rows from a scalar subquery are SQLCODE -811. Use ANY/ALL or IN when the set can have many rows.

Indexability (preview of stage 1 / stage 2)

A matching predicate can probe an index. Stage 1 predicates can be applied on the index or data manager without the full stage 2 residual evaluator. Rough beginner map:

• COL = value — matching if COL is a leading index column
• COL > / >= / < / <= value — range matching on a leading index column
• COL <> value — typically stage 1 not matching
• expression = value (YEAR(COL) = 2000) — often stage 2; rewrite as a range on COL

Details belong on the indexable versus stage 2 page. The takeaway here: write comparisons so the column stands alone on one side whenever you care about access path.

Explain It Like I'm Five

Comparison predicates are questions you ask about two fridge magnets: “Are they the same letter?” (=), “Are they different?” (<>), “Is this one later in the alphabet?” (>). If one magnet is missing (NULL), you do not answer yes or no—you shrug (UNKNOWN), and the WHERE bouncer only lets in the kids who answered yes. Two magnets that look like A, even if one has invisible blank stickers on the end (CHAR padding), still count as the same letter. Measuring “not equal” is a weaker flashlight for finding a magnet in a sorted drawer than asking for the exact letter.

Exercises

1. Predict WHERE COMM > 0 versus WHERE COMM > 0 OR COMM IS NULL on DSN8C10.EMP. Count both.
2. Compare CHAR('A') = 'A' and HEX(CHAR('A')) versus HEX('A'). Why can equality hold when HEX differs in length?
3. Rewrite YEAR(HIREDATE) = 2000 as a pair of >= and < predicates on HIREDATE.
4. Write a row-value predicate that continues after employee 000210 hired on a given date, ordered by HIREDATE, EMPNO.
5. Explain to a reviewer why you changed != to <> in a static SQL package.