DB2 NULL Handling: COALESCE, NULLIF, IS NULL Complete Guide
DB2 NULL Handling: COALESCE, NULLIF, IS NULL Complete Guide
DB2 NULL handling is one of the most subtle and consequential topics in IBM DB2 SQL programming. NULL is not a value — it is the absence of a value, representing unknown or inapplicable information. This distinction has far-reaching consequences: NULL propagates through arithmetic, behaves unexpectedly in comparisons, is silently ignored by most aggregate functions, and can cause NOT IN queries to return zero rows when developers expect thousands. Whether you are writing embedded SQL for COBOL on z/OS or building analytical queries on DB2 LUW, a thorough understanding of NULL handling — including the COALESCE, NULLIF, and VALUE functions — is essential for writing correct, reliable SQL.
What NULL Means in DB2
In relational database theory, NULL means "unknown" or "not applicable." It is not zero, not an empty string, not the string 'NULL' — it is the complete absence of a value. A NULL in a salary column does not mean the employee earns nothing; it means the salary is not recorded. A NULL in a ship date column means the item has not yet shipped.
DB2 stores NULL as a special internal indicator alongside the column value. When you insert a row without providing a value for a nullable column, DB2 stores NULL automatically. When you query that column, NULL behaves according to three-valued logic rather than the familiar Boolean two-valued logic.
NULL vs Zero vs Empty String
-- These are three distinct states in DB2
SELECT EMPNO,
BONUS -- NULL: bonus unknown/not applicable
FROM EMPLOYEE
WHERE BONUS IS NULL;
-- A BONUS of 0 is NOT NULL — it means the employee has a recorded bonus of zero
SELECT EMPNO, BONUS
FROM EMPLOYEE
WHERE BONUS = 0;
-- For VARCHAR columns, empty string '' and NULL are different
SELECT EMPNO, PHONE_EXT
FROM EMPLOYEE
WHERE PHONE_EXT = ''; -- empty string: phone has no extension number
-- vs IS NULL: extension not recordedThree-Valued Logic: TRUE, FALSE, UNKNOWN
Standard SQL (and DB2) uses three-valued logic: a predicate can evaluate to TRUE, FALSE, or UNKNOWN. The WHERE clause only passes rows for which the predicate is TRUE. Rows evaluating to FALSE or UNKNOWN are both excluded.
The critical rule: any comparison involving NULL evaluates to UNKNOWN, not TRUE or FALSE.
5 = 5 → TRUE
5 = 6 → FALSE
5 = NULL → UNKNOWN
NULL = NULL → UNKNOWNThis is why WHERE BONUS = NULL never returns any rows even when rows have NULL bonuses — the comparison evaluates to UNKNOWN, not TRUE. You must always use IS NULL or IS NOT NULL.
Truth Tables for AND, OR, NOT with UNKNOWN
| A | B | A AND B | A OR B | NOT A |
|---|---|---|---|---|
| TRUE | UNKNOWN | UNKNOWN | TRUE | FALSE |
| FALSE | UNKNOWN | FALSE | UNKNOWN | TRUE |
| UNKNOWN | UNKNOWN | UNKNOWN | UNKNOWN | UNKNOWN |
Understanding these tables explains many surprising NULL behaviours in complex WHERE conditions.
IS NULL and IS NOT NULL
The only correct way to test for NULL in DB2 is using the IS NULL and IS NOT NULL predicates.
-- Find employees without a bonus recorded
SELECT EMPNO, LASTNAME, DEPT
FROM EMPLOYEE
WHERE BONUS IS NULL;
-- Find employees who have a bonus recorded (any amount, including 0)
SELECT EMPNO, LASTNAME, BONUS
FROM EMPLOYEE
WHERE BONUS IS NOT NULL;
-- Combine: find employees in Dept A01 with no manager assigned
SELECT EMPNO, LASTNAME
FROM EMPLOYEE
WHERE DEPT = 'A01'
AND MGRNO IS NULL;Using IS NULL in CASE Expressions
-- Classify employee bonus status
SELECT EMPNO,
LASTNAME,
CASE
WHEN BONUS IS NULL THEN 'No Bonus Data'
WHEN BONUS = 0 THEN 'Zero Bonus'
WHEN BONUS < 1000 THEN 'Small Bonus'
ELSE 'Significant Bonus'
END AS BONUS_STATUS
FROM EMPLOYEE
ORDER BY LASTNAME;COALESCE: Replacing NULLs with Default Values
COALESCE(value1, value2, ..., valueN) is the ANSI-standard function that returns the first non-NULL argument from its list. It evaluates arguments left to right and short-circuits as soon as it finds a non-NULL value. COALESCE accepts two or more arguments and all arguments must be compatible data types.
-- Replace NULL bonus with 0 for compensation calculations
SELECT EMPNO,
LASTNAME,
SALARY,
COALESCE(BONUS, 0) AS BONUS_SAFE,
SALARY + COALESCE(BONUS, 0) AS TOTAL_COMP
FROM EMPLOYEE
ORDER BY TOTAL_COMP DESC;Without COALESCE, SALARY + BONUS returns NULL whenever BONUS is NULL, silently dropping the employee's salary from any sum.
COALESCE with Multiple Fallback Values
-- Use first available contact: work phone, then mobile, then home
SELECT EMPNO,
LASTNAME,
COALESCE(WORK_PHONE, MOBILE_PHONE, HOME_PHONE, 'NO CONTACT') AS BEST_PHONE
FROM EMPLOYEE_CONTACTS
ORDER BY LASTNAME;COALESCE walks the list: if WORK_PHONE is not NULL, return it and stop. If it is NULL, check MOBILE_PHONE, and so on. If all are NULL, return the literal string 'NO CONTACT'.
COALESCE in Aggregate Queries
-- Total compensation report — treat NULL bonus as zero
SELECT DEPT,
COUNT(*) AS HEADCOUNT,
SUM(SALARY) AS BASE_PAYROLL,
SUM(COALESCE(BONUS, 0)) AS TOTAL_BONUS,
SUM(SALARY + COALESCE(BONUS, 0)) AS TOTAL_COMPENSATION,
AVG(SALARY + COALESCE(BONUS, 0)) AS AVG_COMPENSATION
FROM EMPLOYEE
GROUP BY DEPT
ORDER BY TOTAL_COMPENSATION DESC;VALUE(): The z/OS Synonym for COALESCE
VALUE(value1, value2, ...) is an IBM DB2 proprietary function that works identically to COALESCE. It is found extensively in legacy z/OS DB2 code written before the SQL standard adopted COALESCE.
-- VALUE() and COALESCE() are interchangeable in DB2
SELECT EMPNO,
VALUE(BONUS, 0) AS BONUS_VALUE, -- DB2 proprietary
COALESCE(BONUS, 0) AS BONUS_COALESCE -- ANSI standard
FROM EMPLOYEE;Recommendation: Use COALESCE in all new code. VALUE is not portable to other databases and is not part of the SQL standard. Both functions perform identically in DB2.
NULLIF: Converting a Specific Value to NULL
NULLIF(expression1, expression2) returns NULL if the two expressions are equal, otherwise returns the value of expression1. It is the logical inverse of COALESCE — where COALESCE replaces NULL with a value, NULLIF converts a specific value into NULL.
-- Convert a sentinel value of 0 back to NULL
-- (useful when legacy systems store 0 to mean "unknown")
SELECT EMPNO,
NULLIF(YEARS_SERVICE, 0) AS YEARS_OR_NULL,
NULLIF(PHONE_EXT, 'N/A') AS PHONE_OR_NULL
FROM EMPLOYEE;NULLIF to Prevent Division by Zero
One of the most important practical uses of NULLIF is preventing division-by-zero errors. Dividing by zero in DB2 raises a SQL error (SQLCODE -802). By replacing a zero denominator with NULL, the division result becomes NULL rather than an error.
-- Calculate conversion rate; avoid division by zero with NULLIF
SELECT CAMPAIGN_NAME,
IMPRESSIONS,
CLICKS,
CONVERSIONS,
DECIMAL(
100.0 * CONVERSIONS / NULLIF(CLICKS, 0),
6, 2
) AS CONVERSION_RATE_PCT,
DECIMAL(
100.0 * CLICKS / NULLIF(IMPRESSIONS, 0),
6, 2
) AS CLICK_THROUGH_RATE_PCT
FROM MARKETING_CAMPAIGNS
ORDER BY CONVERSION_RATE_PCT DESC NULLS LAST;When CLICKS is 0, NULLIF(CLICKS, 0) returns NULL, making the division result NULL rather than throwing an error.
Combining NULLIF and COALESCE
-- Replace sentinel 0 with NULL, then replace NULL with a default
-- Useful when cleaning up legacy data
SELECT EMPNO,
COALESCE(NULLIF(YEARS_SERVICE, 0), -1) AS YEARS_CLEAN
FROM EMPLOYEE;
-- 0 → NULL (NULLIF) → -1 (COALESCE)
-- Any non-zero value stays as-isNULLs in Arithmetic
In DB2, any arithmetic operation that involves a NULL operand produces a NULL result. This is standard SQL behaviour but is the most common source of silent data errors in reporting queries.
-- Demonstrate NULL propagation in arithmetic
SELECT EMPNO,
SALARY,
BONUS,
SALARY + BONUS AS TOTAL_NULL_RISK, -- NULL if BONUS is NULL
SALARY + COALESCE(BONUS, 0) AS TOTAL_SAFE, -- always non-NULL
SALARY * 12 AS ANNUAL_SALARY,
(SALARY * 12) + COALESCE(BONUS * 12, 0) AS ANNUAL_TOTAL
FROM EMPLOYEE
ORDER BY ANNUAL_TOTAL DESC NULLS LAST;The rule is absolute: NULL + anything = NULL. NULL - anything = NULL. NULL * anything = NULL. NULL / anything = NULL.
NULLs in Aggregate Functions
All DB2 aggregate functions except COUNT(*) automatically ignore NULL values.
-- Demonstrate how aggregates handle NULLs
-- Assume 500 rows, 200 have NULL BONUS
SELECT COUNT(*) AS TOTAL_ROWS, -- 500
COUNT(BONUS) AS ROWS_WITH_BONUS, -- 300 (non-NULL only)
SUM(BONUS) AS SUM_BONUS, -- sum of 300 non-NULL values
AVG(BONUS) AS AVG_BONUS_EXCL_NULL, -- sum/300 (not sum/500)
AVG(COALESCE(BONUS, 0)) AS AVG_BONUS_INCL_ZERO -- sum/500
FROM EMPLOYEE;AVG(BONUS) divides the sum by the count of non-NULL rows (300), giving the average bonus among those who have one recorded. AVG(COALESCE(BONUS, 0)) divides the same sum by 500, giving the average across all employees — a materially different and often more useful business metric.
NULLs in JOINs
NULL values in JOIN columns mean those rows do not match any row on the other side of the JOIN. This is correct behaviour but can cause unexpected row counts.
-- INNER JOIN: employees without a matching department are excluded
SELECT E.EMPNO, E.LASTNAME, D.DEPTNAME
FROM EMPLOYEE E
JOIN DEPARTMENT D ON E.DEPT = D.DEPTNO;
-- Employees with NULL DEPT are dropped from results
-- LEFT JOIN: preserve all employees even if DEPT is NULL or has no match
SELECT E.EMPNO, E.LASTNAME, COALESCE(D.DEPTNAME, 'UNASSIGNED') AS DEPT_NAME
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D ON E.DEPT = D.DEPTNO;-- Self-join to find employees and their manager's name
-- Manager with no manager (top of hierarchy) has NULL MGRNO
SELECT E.EMPNO,
E.LASTNAME AS EMPLOYEE,
COALESCE(M.LASTNAME, 'No Manager') AS MANAGER
FROM EMPLOYEE E
LEFT JOIN EMPLOYEE M ON E.MGRNO = M.EMPNO
ORDER BY E.DEPT, E.LASTNAME;NULLs in WHERE Clauses
Because NULL comparisons return UNKNOWN, you must be explicit about how you want to handle NULLs in filter conditions.
-- This finds employees where BONUS is not 1000
-- BUT employees with NULL BONUS are excluded (comparison = UNKNOWN)
SELECT EMPNO, LASTNAME, BONUS
FROM EMPLOYEE
WHERE BONUS <> 1000;
-- To include NULLs in the "not 1000" result:
SELECT EMPNO, LASTNAME, BONUS
FROM EMPLOYEE
WHERE BONUS <> 1000
OR BONUS IS NULL;This is a critical pattern in any reporting query that must include "no data" rows alongside non-matching rows.
NULLs in ORDER BY: NULLS FIRST / NULLS LAST
DB2 supports the ANSI SQL NULLS FIRST and NULLS LAST modifiers in the ORDER BY clause to explicitly control where NULL rows appear in the sorted output.
-- Sort by commission descending, with NULLs at the end
SELECT EMPNO, LASTNAME, COMMISSION
FROM EMPLOYEE
ORDER BY COMMISSION DESC NULLS LAST;
-- Sort by hire date ascending, with unrecorded hire dates at the top
SELECT EMPNO, LASTNAME, HIREDATE
FROM EMPLOYEE
ORDER BY HIREDATE ASC NULLS FIRST;
-- Multiple columns with mixed NULL placement
SELECT EMPNO, DEPT, SALARY, BONUS
FROM EMPLOYEE
ORDER BY DEPT ASC NULLS LAST,
SALARY DESC NULLS LAST,
BONUS DESC NULLS FIRST;On DB2 for z/OS without explicit NULLS FIRST/LAST, NULLs are treated as greater than any non-NULL value — so in an ASC sort they appear last, and in a DESC sort they appear first.
The NOT IN with NULLs Gotcha
This is the most dangerous NULL trap in SQL. When a NOT IN subquery returns any NULL value, the entire NOT IN condition evaluates to UNKNOWN for every row in the outer query, causing the query to return zero rows.
-- DANGEROUS: if MANAGER_ID is NULL in any row of the subquery,
-- this query returns ZERO rows for every employee
SELECT EMPNO, LASTNAME
FROM EMPLOYEE
WHERE EMPNO NOT IN (
SELECT MANAGER_ID -- if any MANAGER_ID is NULL, ALL rows are excluded
FROM PROJECT_TEAM
);
-- SAFE solution 1: add IS NOT NULL filter in subquery
SELECT EMPNO, LASTNAME
FROM EMPLOYEE
WHERE EMPNO NOT IN (
SELECT MANAGER_ID
FROM PROJECT_TEAM
WHERE MANAGER_ID IS NOT NULL -- exclude NULLs from the list
);
-- SAFE solution 2: use NOT EXISTS (NULL-safe by design)
SELECT E.EMPNO, E.LASTNAME
FROM EMPLOYEE E
WHERE NOT EXISTS (
SELECT 1
FROM PROJECT_TEAM PT
WHERE PT.MANAGER_ID = E.EMPNO
);The NOT EXISTS approach is preferred because it is immune to the NULL problem — the inner correlated query simply finds no matching row for the outer employee, rather than evaluating a comparison against NULL.
Inserting and Updating NULLs
Inserting NULL
-- Explicit NULL insert
INSERT INTO EMPLOYEE (EMPNO, LASTNAME, DEPT, SALARY, BONUS)
VALUES ('000999', 'SMITH', 'A01', 55000, NULL);
-- Implicit NULL insert (omit the column from the INSERT list)
INSERT INTO EMPLOYEE (EMPNO, LASTNAME, DEPT, SALARY)
VALUES ('000999', 'SMITH', 'A01', 55000);
-- BONUS defaults to NULL if the column is defined as nullableUpdating to NULL
-- Set a column to NULL to "clear" the value
UPDATE EMPLOYEE
SET BONUS = NULL,
COMM = NULL
WHERE EMPNO = '000999';
-- Conditional update: clear bonus for employees not meeting the threshold
UPDATE EMPLOYEE
SET BONUS = NULL
WHERE PERFORMANCE_RATING < 3
AND BONUS IS NOT NULL;Updating from NULL to a Value
-- Assign default values to all rows where the column is still NULL
UPDATE EMPLOYEE
SET COMM = 0
WHERE COMM IS NULL
AND JOB IN ('SALES', 'ACCOUNT_MGR');NOT NULL Constraint
The NOT NULL constraint prevents a column from ever storing a NULL value. It is defined at the column level in CREATE TABLE or ALTER TABLE.
-- Create a table with NOT NULL constraints
CREATE TABLE ORDERS (
ORDER_ID INTEGER NOT NULL, -- cannot be NULL
CUSTOMER_ID INTEGER NOT NULL, -- cannot be NULL
ORDER_DATE DATE NOT NULL, -- cannot be NULL
AMOUNT DECIMAL(11,2) NOT NULL, -- cannot be NULL
NOTES VARCHAR(500), -- nullable (default)
DISCOUNT DECIMAL(5,2) DEFAULT 0.00, -- defaults to 0, not NULL
PRIMARY KEY (ORDER_ID)
);
-- Add NOT NULL to an existing column (column must have no NULLs first)
ALTER TABLE EMPLOYEE
ALTER COLUMN DEPT SET NOT NULL;If you attempt to insert a NULL into a NOT NULL column, DB2 raises SQLCODE -407.
Practical NULL Handling Example: Customer Report
-- Comprehensive customer summary with safe NULL handling
SELECT C.CUSTOMER_ID,
C.COMPANY_NAME,
COALESCE(C.CONTACT_NAME, 'Unknown Contact') AS CONTACT,
COALESCE(C.PHONE, 'No Phone') AS PHONE,
COALESCE(C.EMAIL, 'No Email') AS EMAIL,
COUNT(O.ORDER_ID) AS TOTAL_ORDERS,
COALESCE(SUM(O.AMOUNT), 0) AS LIFETIME_VALUE,
COALESCE(MAX(O.ORDER_DATE), C.SIGNUP_DATE) AS LAST_ACTIVITY,
DECIMAL(
COALESCE(SUM(O.AMOUNT), 0)
/ NULLIF(COUNT(O.ORDER_ID), 0),
9, 2
) AS AVG_ORDER_VALUE
FROM CUSTOMERS C
LEFT JOIN ORDERS O ON C.CUSTOMER_ID = O.CUSTOMER_ID
AND O.STATUS = 'COMPLETED'
GROUP BY C.CUSTOMER_ID,
C.COMPANY_NAME,
C.CONTACT_NAME,
C.PHONE,
C.EMAIL,
C.SIGNUP_DATE
ORDER BY LIFETIME_VALUE DESC NULLS LAST;This query demonstrates all key NULL handling patterns in one realistic example: COALESCE for display defaults, NULLIF to prevent division by zero, LEFT JOIN to retain customers with no orders, NULLS LAST in ORDER BY, and COALESCE(SUM(...), 0) to show zero instead of NULL for customers with no completed orders.
Quick Reference: DB2 NULL Functions
| Function | Syntax | Purpose |
|---|---|---|
IS NULL | col IS NULL | Test whether a value is NULL |
IS NOT NULL | col IS NOT NULL | Test whether a value is not NULL |
COALESCE | COALESCE(v1, v2, ..., vN) | Return first non-NULL argument (ANSI standard) |
VALUE | VALUE(v1, v2, ..., vN) | IBM synonym for COALESCE (DB2 only) |
NULLIF | NULLIF(expr1, expr2) | Return NULL if expr1 = expr2, else expr1 |
IFNULL | IFNULL(expr, default) | DB2 LUW only; equivalent to COALESCE with two args |
GROUPING | GROUPING(col) | Returns 1 for NULL set by ROLLUP/CUBE, 0 for real NULLs |
NULLS FIRST/LAST | ORDER BY col ASC NULLS LAST | Control NULL position in sorted output |
Summary: Key NULL Rules to Remember
- NULL is not a value — it is the absence of a value.
- Any comparison to NULL produces UNKNOWN, not TRUE or FALSE.
- Always use
IS NULL/IS NOT NULL, never= NULL. COALESCEandVALUE()return the first non-NULL argument — use them to substitute defaults.NULLIFconverts a specific value to NULL — use it to neutralise sentinel values and prevent division by zero.- All aggregate functions (except
COUNT(*)) ignore NULLs — be deliberate about whether you want that behaviour. NOT INwith a subquery containing NULLs returns zero rows — useNOT EXISTSinstead.- NULL columns in JOIN predicates cause rows to be excluded from INNER JOINs — use LEFT JOIN and COALESCE if you need to preserve them.