Hello
I am asking you because I do not understand some things:
In the Document:
Nulls
If a column in a row has no value, then the column is said to be null, or to contain null. Nulls can appear in columns of any data type that are not restricted by NOT NULL or PRIMARY KEY integrity constraints. Use a null when the actual value is not known or when a value would not be meaningful.
Oracle Database treats a character value with a length of zero as null. However, do not use null to represent a numeric value of zero, because they are not equivalent.
Note:
Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.
Any arithmetic expression containing a null always evaluates to null. For example, null added to 10 is null. In fact, all operators (except concatenation) return null when given a null operand.
SELECT CASE WHEN REGEXP_REPLACE('123', '[0-9]', '', 1) = ''
THEN 'NULL'
ELSE 'NOT NULL'
END AS RESULT_WITH_EMPTY
, CASE WHEN REGEXP_REPLACE('123', '[0-9]', '', 1) IS NULL
THEN 'NULL'
ELSE 'NOT NULL'
END AS RESULT_WITH_NULL
FROM DUAL
;
Result:
RESULT_WITH_EMPTY RESULT_WITH_NULL
NOT NULL NULL
Ok.
So, '' (Empty String) is NULL.
But, why there is a need that a NULL can not compare with the equal symbol = ?
Why should we use the IS keyword?
Important Question:
1.) Why do not we get a syntax error if we compare an empty string to something?
-> Actually there is no sense to do such a thing, because it is not going to work right ever.
SELECT CASE WHEN column01 = '' THEN ....
-> We should get an error Message: "Invalid comparing with NULL - Right way is IS NULL"
2.) Why can not Oracle autmatically convert the equal sign to the keyword IS ?
Oracle Convert the Empty string '' to NULL ... and after that the comparing with the equal sign not correct any more.
Can we state that the combination " ... = '' " is generally not allowed in Oracle?
Thanks and Regards,
Hans