Comparison operators in DB2 SQL

What a comparison is

A basic predicate has the shape expression operator expression. The operands must be comparable (compatible types under comparison rules). If either operand is null, the result is unknown. Otherwise the result is true or false. WHERE, HAVING, and ON keep rows only when the search condition is true—unknown is discarded just like false. That is why three-valued logic belongs in the same lesson as =.

The six operators can be rewritten using just = and < if you like algebra: x <> y is NOT (x = y); x > y is y < x; x <= y is x < y OR x = y; x >= y is y < x OR x = y. Db2 still has first-class tokens for all six.

1
2
3
4
5
6
SELECT EMPNO, LASTNAME, SALARY
  FROM HR.EMPLOYEE
 WHERE WORKDEPT = 'A00'
   AND SALARY < 20000
   AND PRSTAFF <> :VAR1
   AND SALARY > (SELECT AVG(SALARY) FROM HR.EMPLOYEE);

Equal (=)

= is true when the two values are equal under the comparison rules for their types. Numbers compare numerically (1.00 equals 1 for DECIMAL vs INTEGER after conversion). Strings compare according to length, padding, and CCSID conversion. Datetime values compare chronologically. Distinct types compare if they are the same distinct type (and not LOB-based).

Character comparison is not always “what an English speaker means by alphabetical.” EBCDIC puts letters and digits in a different code-point order than ASCII. Always know the CCSID before you sort or range-scan character keys.

1
2
3
WHERE EMPNO = '000010'
WHERE SALARY = 50000
WHERE HIREDATE = CURRENT DATE

Never write WHERE COL = NULL. That predicate is unknown for every row. Use IS NULL. The same trap exists for host variables: if the indicator says null, the = comparison does not become “equal to the null in the column”; it becomes unknown.

Not equal (<> and !=)

<> is the standard not-equal operator: true when the values are not equal and both are not null. IBM also accepts product-specific forms !=, ^=, and in some code pages ¬=. Those extra forms exist so old applications keep compiling. They are not recommended for new SQL.

1
2
WHERE WORKDEPT <> 'A00'
-- Avoid in new code:  WHERE WORKDEPT != 'A00'

NOT (x = y) is logically the same as x <> y when neither is null. When nulls are present, NOT of unknown is still unknown, so NOT (COL = 'A') does not pick up the null COL rows either. Use IS NOT NULL if you must include or exclude missing values explicitly.

Less than (<) and greater than (>)

< is true when the left operand is strictly less than the right. > is true when the left is strictly greater. Equality is false for both. For numbers this is ordinary magnitude. For DATE/TIME/TIMESTAMP it is earlier / later. For strings it is the comparison sequence after any conversions—not a linguistic dictionary order unless your collating setup makes it so.

1
2
3
WHERE SALARY < 20000
WHERE HIREDATE > DATE('2015-01-01')
WHERE LASTNAME < 'M'

Range predicates often combine them: SALARY > 20000 AND SALARY < 40000. BETWEEN is a related predicate (covered with other predicates) that includes the endpoints; < and > do not.

Less than or equal (<=) and greater than or equal (>=)

<= is true when the left operand is less than or equal to the right. >= is true when it is greater than or equal. Inclusive ranges use these (or BETWEEN).

Legacy spellings map onto these operators and are easy to misread:

• <= alternatives: ^>, !>, ¬> (“not greater” means less or equal)
• >= alternatives: ^<, !<, ¬< (“not less” means greater or equal)

The ¬ character is only documented for certain code pages (historically 437, 819, and 850). On z/OS EBCDIC source, ¬ can appear in old COBOL SQL. Do not introduce it in new text; type <= and >=.

1
2
3
WHERE SALARY >= 20000
WHERE SALARY <= 40000
WHERE (YEARVAL, MONTHVAL) >= (2009, 10)

Row-value comparisons like (YEARVAL, MONTHVAL) >= (2009, 10) compare left-to-right like a sort key: year first, then month. Support and exact rules follow the SQL Reference for your function level; the idea is “tuple order,” not independent ANDs of each column.

Binary comparisons

Binary comparisons means comparing binary string values (BINARY, VARBINARY, BLOB) byte for byte, and more loosely the idea that Db2 string comparison is code-point / binary after conversion rather than a linguistic collator like some Unicode-aware products.

• Binary types — equal if the byte sequences are equal (length rules for fixed vs varying still apply: shorter VARBINARY vs padded BINARY follows IBM’s binary string comparison rules)
• Character FOR BIT DATA — still a character type for many compatibility rules; do not mix with BINARY in CONCAT; comparison is not a substitute for a true binary type
• X'...' vs BX'...' — hex character constants vs binary constants are different types; comparing them is a type error or a conversion, not “the same bytes in my head”
• BLOBs — comparable as binary strings with the restrictions on LOB predicates in your release; locators can participate in some comparisons via assignment compatibility rules

1
2
3
WHERE BIN_COL = BX'00FF'
WHERE BIN_COL <> BX'0000'
WHERE BIN_COL >= BX'80'

For character columns, “binary comparison” talk usually means: no case folding, no ignoring spaces beyond the type’s pad rules, order by CCSID code points. If you need case-insensitive search, use UPPER/LOWER (and accept the index implications) or a generated column designed for that.

Type rules that bite

• Numbers vs strings — '10' < '9' as characters because '1' compares before '9'; CAST to INTEGER if you mean numeric order
• CHAR vs VARCHAR — trailing blanks on fixed CHAR can make two values compare equal when a VARCHAR-minded programmer expected them not to (and vice versa depending on pad rules)
• Datetime vs string — compare DATE to DATE, or CAST the string
• Distinct types — generated comparison operators apply to the same UDT, not to the source type, and not for CLOB/DBCLOB/BLOB distinct types
• XML — not compared with = the way VARCHAR is; use XML predicates and serialization deliberately

Comparisons versus Boolean operators

= and friends build predicates. AND, OR, and NOT combine those truth values. Mixing them without parentheses is a readability bug even when precedence is defined. Put parentheses around OR groups: (A = 1 OR B = 2) AND C = 3.

Explain It Like I'm Five

A comparison is asking a yes/no question: “are these two stickers the same?” (=), “are they different?” (<>), “is this pile smaller?” (<), “bigger?” (>), or “smaller or the same?” (<=). If one sticker is missing (NULL), the answer is not yes and not no—it is “I cannot tell,” and the row does not get to stay in the WHERE club. != is a slang way some old books wrote “different”; the polite spelling is <>. Binary comparison is lining up toy bricks and checking whether each brick matches, without reading them as words.

Exercises

1. Write a WHERE clause that keeps employees in department D11 with salary at least 50000 using >=.
2. Rewrite WORKDEPT != 'A00' with the standard operator.
3. Explain why WHERE BONUS = NULL returns no rows and write the correct test.
4. Predict whether CHAR 'ABC' = VARCHAR 'ABC' and why trailing blanks might change the answer for CHAR 'ABC '.
5. Write a predicate that compares a VARBINARY column to BX'FF00'.

Frequently asked questions