Aswin Aswin - 6 months ago 10
SQL Question

How to merge two tables in SQL SERVER?

I have the following two tables T1 and T2.

Table T1

Id Value1

1 2
2 1
3 2


Table T2

Id Value2

1 3
2 1
4 1


I need a SQL SERVER query to return the following

Id Value1 Value2
1 2 3
2 1 1
3 2 0
4 0 1


Thanks in advance!!

Answer

You can achieve this by FULL OUTER JOIN with ISNULL

Execution with given sample data:

DECLARE @Table1 TABLE (Id INT, Value1 INT)
INSERT INTO @Table1 VALUES (1, 2), (2, 1), (3, 2)

DECLARE @Table2 TABLE (Id INT, Value2 INT)
INSERT INTO @Table2 VALUES (1, 3), (2, 1), (4, 1)

SELECT ISNULL(T1.Id, T2.Id) AS Id, 
       ISNULL(T1.Value1, 0) AS Value1, 
       ISNULL(T2.Value2, 0) AS Value2
FROM @Table1 T1
FULL OUTER JOIN @Table2 T2 ON T2.Id = T1.Id

Result:

Id   Value1   Value2
1    2        3
2    1        1
3    2        0
4    0        1