When you concatenate any string with a NULL value, it will result in NULL. To avoid this, you can use the COALESCE function.
spark.sql(SELECT COALESCE(Name, '') + ' '+ COALESCE(Column2, '') AS Result FROM table_test).show()
The COALESCE function returns the first non-Null value. So, when there is a value in the column that is not null, that will be concatenated. And if the value in the column is null, then an empty string will be concatenated.