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.