[Oracle] Nulls with Comparison Conditions

Problem
Assume the data in the table is as following:

1 2 3 4

select fyr, age, type1 from ave107fa where paydat between '1041201' and '1041231' order by type1 ;

We can find out the value of type1 has D and null.

If I would like to find the data which Type1 is not eqal to D:

1 2 3 4 5

select fyr, age, type1 from ave107fa where paydat between '1041201' and '1041231' and type1 <> 'D' order by type1 ;

But Oracle return nothing
:


How To
In Oracle,  null represents a lack of data, a null cannot be equal or unequal to any value or to another null. Therefore, it will return nothing.

Hence, we can modify SQL statement as bellows:

1 2 3 4 5

select fyr, age, type1 from ave107fa where paydat between '1041201' and '1041231' and nvl(type1, ' ') <> 'D' order by type1 ;

The data which Type1 is null can be retrieved now:


Reference
[1] https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements005.htm