Nulls the novice and experts trap

Novices and experts alike can fall in the null's hole.

In Oracle there are many reasons for that:


- The nulls are the third state of Boolean variables
- comparing null with any value (including null) gives nulls. Must use IS NULL. So a=b or a!=b do not cover all the options. if "a" or "b" are null the result of any of that comparisons is null;
- Oracle treats the empty string '' as null: so the length of an empty string is null (not 0).
- Any arithmetic expression containing a NULL always evaluates to NULL (null is like an unknown value (not 0) so the results in unknown)
- aggregation operators like sum(), count() disregards nulls values
- in boolean operations the use of null can give null results unless the expression got shortcut (for instance TRUE or null => TRUE)
- comparing null values in columns make the optimizer not use the index
(hint: if IS NULL is required, consider replacing it with a NVL call and a function based index).
- using NOT IN you must be sure the nulls will not appear in the result set of the subquery are else it will return TRUE
(hint: check the column in the subquery against is not null or change the query to use NOT EXISTS)