Does the concatenation of NULL + ‘something’ always yield NULL? Why?

I want to apply an OUTER APPLY computation on every row of a set. This computation concatenates a number of string fields into one but sometimes theses fields are null or filled with empty strings. The concatenation uses ‘-‘ as a delimiter between fields, so when they are empty (”) the result is ‘—-‘ instead of a NULL, the result I’d like to get.

Before doing this computation I need to check the value of these fields.

How would you do it?

I thought about using NULLIF and it seems that it behaves as I expect, but I don’t know why.

Will the concatenation of NULL +’something’ always be NULL? Why?

SELECT 
string_1,
string_2,
string_3,
string_4,
string_5,
string_concat,
FROM Table1
OUTER APPLY(VALUES(NULLIF(string_1,'')+'-'+NULLIF(string_2,'')+'-'+NULLIF(string_3,'')+'-'+NULLIF(string_4,'')+'-'+NULLIF(string_5,''))) v1(string_concat) 

Table1 doesn’t have any index and I can’t implement any.

Is this code better in terms of performance than doing a CASE in the SELECT?

I like it because the code looks more clean, though.

Answer

Does the concatenation of NULL + NULL + ‘something’ always be NULL? Why?

This depends on SET CONCAT_NULL_YIELDS_NULL setting. If it is ON (the default) then yes concatenating a NULL with the + operator will always yield NULL

I’d probably do it like this though

SELECT string_1,
       string_2,
       string_3,
       string_4,
       string_5,
       string_concat,
FROM   Table1
       CROSS APPLY(VALUES (NULLIF(CONCAT(string_1, '-', string_2, '-', string_3, '-', string_4, '-', string_5), '----')) ) v1(string_concat) 

so only one NULLIF is needed – on the CONCAT result.

As the VALUES clause always returns exactly one row you can use CROSS APPLY