NULL Values

NULL is a special value in DB2. It means "unknown" or "not provided". Every column allows NULL unless it is declared NOT NULL.

What NULL means

• NULL is not zero, not a blank, and not an empty string. It means the value is missing or unknown.
• Two NULLs are not equal to each other. NULL = NULL is never true in DB2.
• Arithmetic with NULL gives NULL. Salary + Bonus is NULL if Bonus is NULL.
• By default every column accepts NULL. Add NOT NULL to forbid it.

Finding NULLs in a query

• Use IS NULL to find rows where a column is null. = NULL never works.
• Use IS NOT NULL to find rows where a column has a value.
• In a COBOL program, a null indicator host variable (a SMALLINT) tells the program whether the column was NULL.
• Fetching a NULL column into a plain host variable without an indicator gives SQLCODE -305.

NOT NULL and DEFAULT

• NOT NULL guarantees the column always has a value. Primary key columns are always NOT NULL.
• DEFAULT gives the column a value when INSERT does not provide one, for example DEFAULT 5000.00.
• NOT NULL with DEFAULT is common for audit columns like creation date.

Example

• Finding employees whose commission is unknown, and fetching with a null indicator:-