When is a Null Null

Since the dawn of the computer age 'NULL' has confused and misled developers and designers alike. What makes NULL deserve so much attention and frustration in Oracle.

Let's look at what NULL is for a start.

Null is an unknown value. It is not no value it is indeterminate.

So by that definition we can never know the value of something that is null. It is not less than or greater than a value. In fact a NULL cannot be compared to another NULL for equality or otherwise. One unknown value is not equal to another unknown value neither are they not equal.

This introduces the IS NULL operator and allows a demonstration to sum up what has been said above in a more Oracle centric way.

SELECT 'NULL = NULL' AS "result"
  FROM DUAL
 WHERE NULL = NULL
 UNION ALL
SELECT 'NULL <> NULL' AS "result"
  FROM DUAL
 WHERE NULL <> NULL
 UNION ALL
SELECT 'NULL <= 0' AS "result"
  FROM DUAL
 WHERE NULL <= 0
 UNION ALL
SELECT 'NULL >= 0' AS "result"
  FROM DUAL
 WHERE NULL >= 0
 UNION ALL
SELECT 'NULL IS NULL' AS "result"
  FROM DUAL
 WHERE NULL IS NULL;

result
---------------
NULL IS NULL

The only way to find out if something is null or has a null value is to test it for 'nullness'.