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.
After that it will work.
To know more about it, get your Pyspark certification today and become expert.