Attention! “null” is really nothing..
“null” value in Oracle represents lack of data in the database table cell regardless of column data type. In the database there is no default value if no data specify. No matter what type of column, “null” value indicates empty cell.
To do focus the topic I want to share my real life experiences and demonstrate what can be done avoid “null” traps :)
Let’s try to show how oracle treats;
Imagine that we have simple user table includes 15 users in a department, and manager_id column has value id of manager.
- what if we use Oracle count() function: Even table has 15 records both count(manager_id) and count(distinct manager_id) functions does not consider “null” values existence.
- All equivalent operators like “=” “!=” returns “false” against “null” value due to there is not anything to compare.
- Especially be careful by using “not in” operator: Think that we desire to retrieve all users are not reported to manager with ids 3005 and 3009. Although we are expecting 3 records below query returns 2 records because of the “null” value.
Reason of the situation, as mentioned above, all equivalent operators return “false”. To do achieve the problem and retrieve desired/correct result use/consider “null” values like below.
All below queries retrieve same and correct result.
- Use “is null” “is not null” expressions dealing with “null” values.
Have a nice day without “null” traps :)
