IS NULL and IS NOT NULL in DB2 SQL

The NULL predicate

IBM’s SQL Reference defines the NULL predicate as a test for null values:

1
expression IS [NOT] NULL

The result cannot be unknown. If the expression is null, IS NULL is true; if it is not null, the result is false. IS NOT NULL reverses that. That is why WHERE can keep or drop rows reliably: unlike COMM = 0, this predicate never yields UNKNOWN.

A parameter marker must not be specified for or within the expression. You test a column, a host variable, an expression, or (in supported contexts) an array. You do not write ? IS NULL as the whole predicate in the way beginners sometimes try.

1
2
3
4
5
6
7
SELECT EMPNO, LASTNAME, COMM
  FROM DSN8C10.EMP
 WHERE COMM IS NULL;
 
SELECT EMPNO, LASTNAME, COMM
  FROM DSN8C10.EMP
 WHERE COMM IS NOT NULL;

IBM’s sample: PHONENO IS NULL is true whenever PHONENO has the null value, and false otherwise. Arrays can be null as a whole: MYARRAY IS NULL tests the array value, not whether an element is null.

ISNULL and NOTNULL

For compatibility with other SQL dialects, Db2 allows ISNULL as an alternative for IS NULL and NOTNULL for IS NOT NULL. New z/OS SQL should use the two-word forms; they match the manuals, code reviews, and every IBM sample.

Why = NULL does not work

The equal operator compares two values. Null is not a value in that sense; any comparison that involves a null operand is UNKNOWN (except the special IS DISTINCT FROM predicate). WHERE and HAVING keep rows only when the search condition is TRUE.

1
2
3
4
5
6
7
8
9
10
11
-- Wrong: never keeps null commissions
WHERE COMM = NULL
 
-- Wrong: also UNKNOWN when COMM is null
WHERE COMM = 0
 
-- Right: missing commission
WHERE COMM IS NULL
 
-- Right: stored zero (a real number)
WHERE COMM = 0

The same trap appears in ON, CHECK, CASE searched-when, and MERGE. If the business question is “missing,” write IS NULL. If the question is “equal to this known value,” write a basic predicate and accept that nulls will not match.

IS DISTINCT FROM is the null-aware equality test (two nulls are not distinct). Use it when you need “same including both null” without writing OR IS NULL pairs. For “is this column empty of data,” IS NULL remains the clear predicate.

IS NOT NULL

IS NOT NULL means the expression has a value. That value might be zero, a blank string, a zero-length VARCHAR, or a real commission. It is not a test for “interesting” data.

1
2
3
4
SELECT EMPNO, LASTNAME
  FROM DSN8C10.EMP
 WHERE COMM IS NOT NULL
   AND COMM > 0;

Combining IS NOT NULL with a range is optional for WHERE COMM > 0, because a null COMM already fails the comparison. Writing both can still help readers and some access paths (COL IS NOT NULL is listed among stage-1 range-related predicates in performance summaries). Do not use IS NOT NULL as a substitute for a domain check: a CHAR column of all blanks is NOT NULL and still “empty” to the application.

NULL versus empty string reminder

This is the beginner mix-up that produces empty QMF reports.

• NULL — no value. IS NULL is true. Length functions on a null return null, not zero.
• Empty VARCHAR — a present string with length 0. IS NULL is false. LENGTH is 0. Equality to '' is true.
• CHAR(n) — cannot hold a zero-length string. An “empty” CHAR is usually n blanks. IS NULL is false unless the column is actually null. Comparing CHAR to '' follows padding rules and often surprises people.

1
2
3
4
5
6
7
8
9
10
11
-- Missing phone
WHERE PHONENO IS NULL
 
-- Present but blank CHAR (if the column is CHAR)
WHERE PHONENO = ''
-- or, more honestly for CHAR:
WHERE PHONENO = '      '   -- width matches the column
 
-- Present zero-length VARCHAR
WHERE PHONENO = ''
  AND PHONENO IS NOT NULL;

Application copybooks often treat spaces as “no phone.” The database may store NULL, blanks, or a default. Agree on one convention. Mixing them means you need PHONENO IS NULL OR PHONENO = '' (and maybe TRIM) forever.

Nulls in programs: indicator variables

SELECT INTO and FETCH into a host variable that cannot represent null (a COBOL PIC without an indicator) fails with SQLCODE -305 when the column is null. Declare a SMALLINT indicator and test it after SQLCODE 0:

• Indicator negative (usually -1) — the column was null; ignore the host variable contents.
• Indicator zero — a real value was assigned.
• Positive indicator — truncation on some string assignments.

SQL PL and SQL procedures use IS NULL on SQL variables the same way. Do not compare a variable to the NULL keyword with equals.

Outer joins and “unmatched” rows

After a LEFT OUTER JOIN, unmatched preserved rows have nulls on the other side. The usual anti-join pattern is:

1
2
3
4
5
SELECT D.DEPTNO, D.DEPTNAME
  FROM DSN8C10.DEPT D
  LEFT JOIN DSN8C10.EMP E
    ON E.WORKDEPT = D.DEPTNO
 WHERE E.EMPNO IS NULL;

Test a column that is NOT NULL in the inner table (primary key). Testing a nullable inner column cannot tell “no match” from “match with a null in that column.”

CHECK constraints and indexes

A column defined NOT NULL never needs IS NULL in queries against that column—the predicate would be false for every row. Nullable columns that you often search with IS NULL / IS NOT NULL can still use indexes; null handling in type-2 indexes is designed for this. Sparse “mostly null” columns sometimes belong in a separate table rather than a wide nullable column you always IS NOT NULL away.

Explain It Like I'm Five

Imagine lunch boxes. Some boxes have an apple (a real value). Some boxes have a note that says “apple not packed today” (NULL). Some boxes contain a crumpled empty bag (empty string). Asking “is the apple equal to nothing?” is a confusing riddle. Asking “is the not-packed note in the box?” (IS NULL) is a yes/no question. Empty bags are still bags. Blank CHAR fields are boxes stuffed with packing peanuts so they look full but have no apple.

Exercises

1. Rewrite WHERE COMM = NULL and WHERE COMM <> NULL into legal predicates.
2. Write a query that lists employees with a missing phone number, then one that lists employees whose CHAR phone is all blanks (assume CHAR(6)).
3. After a LEFT JOIN from DEPT to EMP, write the IS NULL test that finds departments with no employees.
4. Explain SQLCODE -305 on SELECT INTO of COMM when the employee has no commission.
5. Decide whether VARCHAR '' should be allowed in a column that also allows NULL, and what predicates your programs would need.

Frequently asked questions

Is NULL a data type?

No. Null is a property of a value (or the absence of one). Columns have types and may allow nulls unless NOT NULL was specified.

Can I use IS NULL on an expression?

Yes. SALARY + COMM IS NULL is true if either operand is null (null arithmetic yields null). That is different from testing the two columns separately.

Does COUNT(*) count null rows?

COUNT(*) counts rows, including rows whose other columns are null. COUNT(COL) ignores nulls in COL. COUNT(*) WHERE COL IS NULL counts rows where that column is null.