The STRING_AGG function ignores NULL values; in the result, they are represented by an empty string. Therefore, the following two statements involved in the UNION ALL operator return the same output:
SELECT STRING_AGG(c,',') AS fav_city FROM (VALUES('Vienna'),('Lisbon')) AS T(c)
UNION ALL
SELECT STRING_AGG(c,',') AS fav_city FROM (VALUES('Vienna'),(NULL),('Lisbon')) AS T(c);
Here is the output:
fav_city
---------------------------------
Vienna,Lisbon
Vienna,Lisbon
If you want to represent NULLs in the outputted string, you need to replace them with a desired value by using the ISNULL or COALESCE functions:
SELECT STRING_AGG(c,',') AS fav_city FROM (VALUES('Vienna'),('Lisbon')) AS T(c)
UNION ALL
SELECT STRING_AGG(COALESCE(c,'N/A'),',') AS fav_city FROM (VALUES('Vienna'),(NULL),('Lisbon')) AS T(c);
Now the output is different, NULL is represented with N/A:
fav_city
---------------------------------
Vienna,Lisbon
Vienna,N/A,Lisbon