SQL NULL values || DB2 for i SQL

Handling NULL values in DB2 for i SQL

NULL value means that there is no value present for the column in a row in table. It is an unknown value. We cannot relate it with zero or blank.

NULL value can be used as a condition in the WHERE and HAVING clauses.

IS NULL predicate is used to check for NULL values.

Create table NULLFILE

CREATE TABLE NULLFILE (
  IdNo INT PRIMARY KEY,
  Name VARCHAR(20) ,
  City VARCHAR(20))

Insert records in NULLFILE table as below

INSERT query:

INSERT INTO NULLFILE (IdNo, Name, City) 
             VALUES                     
(1, 'Amit', 'PATNA'),                   
(2, 'Anil', 'PATNA')                    

Below insert will insert NULL values inside column name CITY as we are not providing value for that column in the INSERT query.

INSERT INTO NULLFILE (IdNo, Name)    
             VALUES                  
(3, 'Amar'  ),                       
(4, 'Ankur' )                         

NULLFIL Table data:

IDNO   NAME                  CITY  
   1   Amit                  PATNA 
   2   Anil                  PATNA 
   3   Amar                  -     
   4   Ankur                 -     

Example 1: Select the values for all rows that contain a null value for the City

SELECT * FROM nullfile WHERE CITY IS NULL

Output:

IDNO   NAME                  CITY
   3   Amar                  -   
   4   Ankur                 -   

Example 2: Select the values for all rows that does nor contain a null value for the City

SELECT * FROM nullfile WHERE CITY IS NOT NULL

Output:

IDNO   NAME                  CITY 
   1   Amit                  PATNA
   2   Anil                  PATNA