DB2 - SQL Null Values

What is a NULL Value?

Some columns cannot have a meaningful value in every row. DB2 uses a special value indicator, the null value, to stand for an unknown or missing value. A null value is a special value that DB2 interprets to mean that no data is present.

A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation.

How Null Values created?

If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.

If you do not specify otherwise,DB2 allows any column to contain null values. Users can create rows in the table without providing a value for the column.

How to prevent Null Values?

Using the NOT NULL clause enables you to disallow null values in the column. Primary keys must be defined as NOT NULL.

How to Test for NULL Values?

IS NULL and IS NOT NULL operators is used to test for NULL values.

Syntax: IS NULL

SELECT column_names
  FROM table_name
  WHERE column_name IS NULL;

Syntax: IS NOT NULL

SELECT column_names
  FROM table_name
  WHERE column_name IS NOT NULL;

DB2 Database:

Below is a selection from the "Product" table in the DB2 database:

Example 1:

The following SQL lists all ProductID with a NULL value in the "Category" field.

SELECT ProductID
   FROM Product
   WHERE Category IS NULL;

Result:

Example 2:

The following SQL lists all ProductID with a value in the "Category" field.

SELECT ProductID
   FROM Product
   WHERE Category IS NOT NULL;

Result:

If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!