Concatenating with NULLs in SQL

A concatenation of 2 or more values in SQL looks like a pretty simple operation. It's enough to use '||' operator which comes in SQL standard. Well, it turns out that this is not so trivial in case of NULL values.