TSO-ISPF JCL COBOL VSAM DB2 CICS Tools Articles Job Portal Forum Quiz Interview Q&A

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:

ProductIDProductDescCategorySRPQtyOnHandTotalValue
7001MouseAccessories75.00
7002Harddrive65.00201,300
7003KeyboardAccessories36.00331,118.00
7004RamComponents23.5016376.00
7005HondaBikes1,200
7006Pen7.451074.50

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:
ProductID
7002
7006


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:
ProductID
7001
7003
7004
7005


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

Are you looking for Job Change? Job Portal