Expressions and operators overview in DB2

Expressions

An expression produces a value. Without operators, the expression is that value: a column name, a host variable, a string or number literal, a function result, or a scalar fullselect.

1
2
3
4
5
6
SELECT SALARY,
       :HV_SALARY,
       'SALARY',
       MAX(SALARY),
       SALARY * 1.03 AS RAISED
  FROM HR.EMPLOYEE;

Expressions appear in SELECT lists, SET assignments, VALUES rows, ORDER BY, and nested inside predicates and CASE. The data type of the result follows Db2’s type resolution rules—promotion, casting, and function resolution (next pages) decide the final type.

Richer expression forms include CASE, CAST, array constructors, OLAP specifications, and sequence references. Start with simple arithmetic and concatenation; add the rest as you need them.

Operators at a glance

Arithmetic operators

Use +, −, *, and / on numeric operands (and certain datetime + duration patterns). Unary plus and minus set signs. Division by zero and numeric overflow raise errors according to product rules. Mixing SMALLINT with DECIMAL promotes types—see the casting and numeric-types pages.

1
2
3
4
5
SELECT EMPNO,
       SALARY + COALESCE(COMM, 0) AS TOTAL,
       SALARY * 1.10 AS AFTER_RAISE,
       (SALARY - 20000) / 12 AS MONTHLY_OVER_BASE
  FROM HR.EMPLOYEE;

Concatenation operators

String concatenation joins compatible strings. Prefer the keyword CONCAT over ||. Vertical bars (and some EBCDIC substitutes) can break when statements move across CCSIDs; CONCAT travels more safely. If either operand is null, the result is null.

1
2
SELECT FIRSTNME CONCAT ' ' CONCAT LASTNAME AS FULL_NAME
  FROM HR.EMPLOYEE;

Character and binary strings do not concatenate with each other. Result length and type (CHAR vs VARCHAR vs CLOB, and graphic counterparts) follow documented combination tables.

Comparisons

Comparison operators (=, <>, <, >, <=, >=) compare two expressions and produce a predicate result—not a numeric “1/0” value in ordinary SQL. Operands must be compatible types (all numeric types are compatible with each other; strings compare with strings; datetimes with datetimes, with conversion rules).

1
2
3
WHERE SALARY >= 50000
WHERE HIREDATE < CURRENT DATE
WHERE LASTNAME <> FIRSTNME

Distinct types usually must match exactly or be cast before comparison. Constants are built-in types, so comparing a distinct-typed column to a literal often needs CAST.

Boolean logic

AND, OR, and NOT combine predicate truth values into search conditions. They are not arithmetic operators: you do not write SALARY AND COMM to mean a numeric calculation. Under three-valued logic, UNKNOWN participates in the truth tables you met on the NULL page.

1
2
3
WHERE WORKDEPT = 'A00'
  AND (SALARY > 60000 OR BONUS > 1000)
  AND NOT (COMM IS NULL);

Operator precedence

When expressions mix operators without parentheses, precedence and associativity decide evaluation order. Arithmetic multiplies before it adds. Boolean NOT binds tighter than AND, which binds tighter than OR—but always parenthesize complex filters so humans (and future you) read the intent instantly.

1
2
3
4
5
6
7
8
-- 14, not 20
VALUES 2 + 3 * 4;
 
-- Force addition first → 20
VALUES (2 + 3) * 4;
 
-- Clarify Boolean intent
WHERE (A = 1 OR B = 2) AND C = 3;

Putting it together

1
2
3
4
5
6
7
SELECT EMPNO,
       FIRSTNME CONCAT ' ' CONCAT LASTNAME AS NAME,
       SALARY * 1.03 AS NEXT_SALARY
  FROM HR.EMPLOYEE
 WHERE WORKDEPT IN ('A00', 'B01')
   AND SALARY + COALESCE(COMM, 0) > 55000
 ORDER BY NEXT_SALARY DESC;

The SELECT list uses concatenation and arithmetic expressions. The WHERE clause uses comparison and Boolean operators (plus an IN predicate). Precedence makes SALARY * 1.03 obvious; COALESCE protects the sum from null COMM.

Explain It Like I'm Five

An expression is a recipe that makes a number or a word: “take salary and multiply by one and a tiny bit.” Operators are the action words in the recipe—add, multiply, stick words together. Comparisons ask “is this pile bigger than that pile?” Boolean words like AND and OR glue those questions together: “is it red AND big?” Precedence is the rule about which action happens first when you forget to use parentheses—like always multiplying before adding in school math.

Exercises

1. Write an expression for yearly pay if SALARY is monthly: SALARY * 12.
2. Build FULL_NAME with CONCAT from FIRSTNME and LASTNAME with a space between.
3. Evaluate 10 - 2 * 3 and then (10 - 2) * 3.
4. Explain why WHERE A = 1 OR B = 2 AND C = 3 is ambiguous to readers and how parentheses fix it.
5. Predict the result of 'A' CONCAT NULL.