How to merge all column data from multiple table in to one single table

Question

I have multiple tables and have different data. I want merge all column data from multiple table in to one single table. It is like union all, but, it will not giving proper result.

can anyone pls. suggest what type of sql query have to use...?

below is the example data.

Input Table

Output (Result)

pls. tell me the better way to get the result using SQL Query..

Answer 1

Try this type:

select coalesce(t1.EmpNo, t2.EmpNo, t3.EmpNo) as EmpNo, t1.EmpName, t1.Dept, t2.Country, t2.SAL, t3.Age, t3.Class
from [EmpTable-1] t1
full outer join [EmpTable-2] t2 on t2.EmpNo = t1.EmpNo
full outer join [EmpTable-3] t3 on t3.EmpNo = t1.EmpNo or t3.EmpNo = t2.EmpNo
order by EmpNo

Answer 2

Something like this?

;WITH cte AS
    (SELECT DISTINCT EmpNo
     FROM EmpTable1
     UNION
     SELECT DISTINCT EmpNo
     FROM EmpTable2
     UNION
     SELECT DISTINCT EmpNo
     FROM EmpTable3)
SELECT T1.EmpNo, 
       T1.EmpName, T1.Dept,
       T2.Country, T2.SAL,
       T3.Age, T3.Class
FROM cte 
     LEFT JOIN
     EmpTable1 AS T1
         ON cte.EmpNo = T1.EmpNo
     LEFT JOIN
     EmpTable2 AS T2
         ON T1.EmpNo = T2.EmpNo
     LEFT JOIN
     EmpTable3 AS T3
         ON T1.EmpNo = T3.EmpNo