Generally speaking, NULL is not a special value, but it represents an undefined value. Given this, comparing anything against NULL is not applicable. Therefore, any comparison of the form "column = NULL" always returns NULL in Exasol, even if that column contains NULL values. Especially joins do not generate matches on rows where the join condition contains NULL values.
If a value is to be tested against NULL, the comparison operator has to be replaced by the IS NULL and IS NOT NULL predicates.
The following sample table will be used in all the examples on this page to demonstrate NULL handling.
This function is not primarily designed for NULL handling, it will return the first retX value for which expr=valX holds true. However, the function is exceptional in the sense that it will match NULL values when asked to do so.
The equivalent CASE-expression would have to fall back on the corresponding NULL-Predicate:
CASE WHEN expr=val1 THEN ret1 WHEN expr is NULL then ... ELSE default END
If no comparison against NULL is required, the following expression also is equivalent:
CASE expr WHEN val1 THEN ret1 WHEN val2 THEN ... ELSE default END
Now we can make some different comparisons to our table:
zeroifnull(num) + 1 num1,
nvl(cast(boo AS varchar(10)), 'unknown'),
coalesce(str, 'X') str,