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.
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.
Using the NOT NULL clause enables you to disallow null values in the column. Primary keys must be defined as NOT NULL.
IS NULL and IS NOT NULL operators is used to test for NULL values.
SELECT column_names FROM table_name WHERE column_name IS NULL; |
SELECT column_names FROM table_name WHERE column_name IS NOT NULL; |
Below is a selection from the "Product" table in the DB2 database:
ProductID | ProductDesc | Category | SRP | QtyOnHand | TotalValue |
---|---|---|---|---|---|
7001 | Mouse | Accessories | 75.00 | ||
7002 | Harddrive | 65.00 | 20 | 1,300 | |
7003 | Keyboard | Accessories | 36.00 | 33 | 1,118.00 |
7004 | Ram | Components | 23.50 | 16 | 376.00 |
7005 | Honda | Bikes | 1,200 | ||
7006 | Pen | 7.45 | 10 | 74.50 |
The following SQL lists all ProductID with a NULL value in the "Category" field.
SELECT ProductID FROM Product WHERE Category IS NULL; |
ProductID |
---|
7002 |
7006 |
The following SQL lists all ProductID with a value in the "Category" field.
SELECT ProductID FROM Product WHERE Category IS NOT NULL; |
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!