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 ALLSELECT STRING_AGG(c,',') AS fav_city FROM (VALUES('Vienna'),(NULL),('Lisbon')) AS T(c);
Here is the output:
fav_city---------------------------------Vienna,LisbonVienna,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 ALLSELECT STRING_AGG(COALESCE(c,'N/A'),',') ...
