Sum of Columns of Two Tables Into One Column in SQL Server 2012

This article will give you an idea of how to add columns of two tables into one column and also defines how to handle a null value in SQL Server. There is no need to write an insert query again and again; you can do it using a single query. Here, I have constructed a query that provides a solution for adding columns of multiple tables into one column. Let's take a look at a practical example. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

We create two tables with the same id columns. The first table is named UserTable with the columns ID and Name and another table named TableUser with the columns ID and UserName.

The tables looks as in the following:

UserTable

TableUser

In the preceding, tables id is the common columns. We add ID columns of both tables, for example:

2+2=4
55+5=10

Now the following is the simple example to add columns of multiple tables into one column using a single Full join:

select T1.ID as TableUserID, T2.id as TableUserID,T1.Id+T2.Id as AdditonResult
from UserTable as T1
Full join tableuser as T2
on T1.name = T2.UserName

Now Press F5 to run the query and select the query to see the result.

Problem

In the preceding image we see UserTable has 4 columns and table TableUser has 2 columns. So it uses a NULL value as the result. Such as:

9+ NULL=NULL

11+NULL=NULL

Solution

You can use a SQL "ISNULL" or "COALESCE" function to handle a NULL value. These functions are explained below.

ISNULL() Function in SQL Server

The "ISNULL()" function is used to replace a NULL with the specified replacement value. This function contains only two arguments.

Syntax

ISNULL (check_exp, change_value)

Example

Declare @name varchar=null
select isnull(@name, '0') AS ISNULLResult

Output

Coalesce() Function in SQL Server

The "Coalesce()" function returns the first non-null value among its arguments. This function doesn't limit the number of arguments, but they must all be of the same data type.

Syntax

COALESCE ( expression [ ,...n ] )

Example

Declare @name varchar=null
select COALESCE(@name, '0') AS COALESCEResult

Output

Now we use the ISNULL Function to convert a NULL value to 0 to find the sum of all columns. The following is the simple example to add columns of multiple tables into one column using a single full join using the ISNULL Function.

SELECT ISNULL(T1.ID,0) as UserTableID , isnull(T2.Id,0) as TableUserID, ISNULL(T1.Id,0)+ isnull(T2.Id,0) as AdditonResult
FROM UserTable T1
FULL JOIN tableuser T2
ON T1.name = T2.UserName

Now Press F5 to run the query and select the query to see the result.