As the correct output is Null,'Is Zero', 'Is One', 'Is Two'.
I want to know why NULL is also the part of the output.
The explanation says that:
The statement CASE COL1 WHEN NULL THEN 'Is Null' doesn't evaluate to TRUTH value as NULL represents MISSING value.
Hence it returns NULL.
As the statement CASE COL1 WHEN NULL THEN 'Is Null' is not evaluated to TRUTH,then in that case it should not return any values as there is no else condition mentioned in the statement.
Kindly help me to understand this.
The answer is in the else condition -- if there's no else, then the result is null.
You can verify this by adding an additional value in the tmp subquery, say 10 or 42), you'll get nulls for those rows.