SOLVE NULL + Text = NULL OR NULL + Int = NULL

Scenario: 

I have two columns in my table which are of type varchar, Now when I try to concatenate these two columns where one of columns have NULL it will lead to NULL which is wrong. Same thing with columns of type Int, if I try adding NULL int with non NULL int it will lead to NULL which is wrong.

From above data if I concatenate Text1 with Text2 it will result to NULL. Query for concatenating and its result is as follows:

Query:

Select Text1+Text2 As [Text1+Text2] from [dbo].[Null_Test]

Result: 

Solution: 

Replace NULL with empty string '' which will solve above issue. To achieve this you can use ISNULL or Coalesce. Below are two queries with ISNULL and Coalesce.

ISNULL Query:

Select Text1+ISNULL(Text2,'') As [Text1+Text2] from [dbo].[Null_Test]

Result: 

Coalesce Query:

Select Text1+Coalesce(Text2,'') As [Text1+Text2] from [dbo].[Null_Test]

Result: 

From above data if I concatenate Num1 with Num2 it will result to NULL. Query for adding and its result is as follows:

Query:

Select Num1+Num2 As [Num1+Num2] from [dbo].[Null_Test]

Result: 

Solution: 

Replace NULL with 0 which will solve above issue. To achieve this you can use ISNULL or Coalesce. Below are two queries with ISNULL and Coalesce.

ISNULL Query:

Select Num1+ISNULL(Num2,0) As [Num1+Num2] from [dbo].[Null_Test]

Result: 

Coalesce Query:

Select Num1+Coalesce(Num2,0) As [Num1+Num2] from [dbo].[Null_Test]

Result:

Conclusion: 

Null + anything is NULL in SQL Server.