NULL and three-valued logic in DB2

What NULL is

In Db2, NULL denotes the absence of a (non-null) value. It is available for every data type as a marker, not as an ordinary typed constant with a length and CCSID. The SQL keyword NULL is a null constant: it has no data type of its own until context (or CAST) provides one.

1
2
3
4
5
6
INSERT INTO HR.EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT)
VALUES ('300001', 'JOHN', NULL, 'SMITH', 'A00');
 
UPDATE HR.EMPLOYEE
   SET COMM = NULL
 WHERE EMPNO = '300001';

Sources that cannot provide null include ordinary constants (other than the NULL keyword), columns defined as NOT NULL, and special registers. Aggregate COUNT and COUNT_BIG never return null (they return zero when no rows qualify). ROWID columns do not store nulls, though a query can still produce a null ROWID in some result situations.

Contrast NULL with look-alikes:

• 0 — a known numeric quantity
• '' — a known empty character string
• NULL — unknown or not applicable; not equal to 0 or ''

NULL semantics in tables and statements

Column definitions

When you CREATE TABLE, decide nullability deliberately. Primary key columns must be NOT NULL. Optional attributes (middle initial, commission, termination date) are classic nullable columns. If unknown values are unacceptable for a column, use NOT NULL and perhaps a DEFAULT so programmers always see a concrete value.

1
2
3
4
5
6
7
CREATE TABLE HR.DEPT (
  DEPTNO   CHAR(3)  NOT NULL,
  DEPTNAME VARCHAR(36) NOT NULL,
  MGRNO    CHAR(6),                 -- nullable: unknown manager
  ADMRDEPT CHAR(3)  NOT NULL,
  PRIMARY KEY (DEPTNO)
) IN MYDB.MYTS;

Defaults versus nulls

NOT NULL WITH DEFAULT (or DEFAULT clauses) tell Db2 what to store when an INSERT omits the column. That is different from storing NULL. Defaults are real values; nulls remain “no value.” Pick the model your business means: “unknown commission” (NULL) versus “commission treated as zero until set” (DEFAULT 0).

Expressions and functions

Many scalar expressions return NULL if an operand is NULL (for example, arithmetic with a null salary). Functions such as COALESCE and IFNULL replace nulls with substitutes. NULLIF can create a null when two expressions are equal. Learn these tools early—they are how you tame three-valued logic in reports.

1
2
3
4
SELECT EMPNO,
       COALESCE(COMM, 0) AS COMM_FOR_REPORT,
       SALARY + COALESCE(COMM, 0) AS TOTAL_PAY
  FROM HR.EMPLOYEE;

Three-valued logic

Programming languages often use Boolean true/false only. SQL predicates use three-valued logic:

• TRUE — condition holds
• FALSE — condition fails
• UNKNOWN — cannot tell, usually because a null participated

For a basic predicate such as expression1 = expression2, if either operand is null, the result is UNKNOWN (also if a scalar subquery is empty in contexts the rules treat that way). UNKNOWN is not the same as FALSE.

WHERE keeps only TRUE

The WHERE clause builds a result from rows for which the search condition is TRUE. Rows where the condition is FALSE or UNKNOWN are discarded. That single rule explains why “null salaries disappear” when you write WHERE SALARY > 50000: for null SALARY the comparison is UNKNOWN, so the row is filtered out—even though the salary is not “less than or equal to 50000” in ordinary English.

1
2
3
4
5
6
7
8
9
10
-- Rows with NULL SALARY are NOT returned
SELECT EMPNO, SALARY
  FROM HR.EMPLOYEE
 WHERE SALARY > 50000;
 
-- Explicitly include or exclude nulls
SELECT EMPNO, SALARY
  FROM HR.EMPLOYEE
 WHERE SALARY > 50000
    OR SALARY IS NULL;

AND, OR, and NOT with UNKNOWN

Memorize the short version: FALSE dominates AND; TRUE dominates OR; NOT cannot turn UNKNOWN into TRUE or FALSE. When you combine predicates, one UNKNOWN term can wipe out a filter you thought was decisive—or, with OR TRUE, become irrelevant.

Testing for NULL correctly

The NULL predicate is the supported way to ask whether something is null:

1
2
3
4
5
6
7
SELECT EMPNO
  FROM HR.EMPLOYEE
 WHERE COMM IS NULL;
 
SELECT EMPNO
  FROM HR.EMPLOYEE
 WHERE COMM IS NOT NULL;

Anti-patterns:

1
2
3
4
5
6
-- WRONG: does not find nulls the way you think
WHERE COMM = NULL;
WHERE COMM <> NULL;
 
-- For "null-safe" equality of two expressions, learn IS NOT DISTINCT FROM
WHERE COL1 IS NOT DISTINCT FROM COL2;

NULL = NULL is UNKNOWN, so two nulls are not “equal” under =. If your business rule is “treat matching nulls as the same,” use IS NOT DISTINCT FROM or write explicit IS NULL pairs.

Nulls in joins, sorting, and constraints

Outer joins manufacture nulls for unmatched sides—three-valued logic shows up immediately in join predicates and later filters. Unique constraints and primary keys disallow nulls in key columns. Foreign keys treat a composite key as null if any component is null, with special referential rules. ORDER BY places nulls according to Db2 sorting rules (nulls sort differently than non-null values)—never assume nulls are “zero” in reports without checking.

In application programs, pair each nullable host variable with an indicator variable. After FETCH or SELECT INTO, a negative indicator means the column was null; do not trust the main host field until you check. Ignoring indicators is one of the classic production defects on the mainframe.

Explain It Like I'm Five

Imagine asking “Is the cookie jar taller than this bottle?” If someone hid the jar and you cannot see it, you cannot answer yes or no—you answer “I don’t know.” That “I don’t know” is NULL / UNKNOWN. The lunch line only lets people through when the answer is clearly yes (TRUE). “I don’t know” does not get you through—just like WHERE drops UNKNOWN rows. To ask “is the jar missing?” you must use a special question: IS NULL—not “does the jar equal missing?” which still means “I don’t know.”

Exercises

1. Predict which rows return for WHERE COMM > 1000 when COMM can be null, 0, 500, or 2000.
2. Rewrite a mistaken WHERE COL = NULL into a correct null test.
3. Evaluate TRUE AND UNKNOWN and FALSE OR UNKNOWN.
4. Write a SELECT that lists employees with unknown middle initials using IS NULL.
5. Explain why COUNT(COMM) and COUNT(*) can differ when COMM is nullable.