How to UNION tables that are not union-compatible
Is there any way to UNION two tables that are not union-compatible?
Is there any way to UNION two tables that are not union-compatible?
Table1: Name, Id, Tel
Table2: Name, Dept
Thanks.
Yes, all you need to do is use some "placeholder" values. My clear preference is to use NULLs for this purpose:
select Name , Id , Tel , cast(null as varchar) as Dept from Table1 union all select Name , null , null , Dept from Table2
Another solution suggests itself from the mere fact that you seem to be combining data about employees, and that's a FULL OUTER JOIN:
select coalesce(t1.Name,t2.Name) as Name , t1.Id , t1.Tel , t2.Dept from Table1 as t1 full outer join Table2 as t2 on t1.Name = t2.Name
This will produce the same result as the UNION, including NULLs in the same places, with one exception. If the same Name exists in both Table1 and Table2, in the UNION result set you will have two rows for each pair of names, whereas in the FULL OUTER JOIN they'll be "collapsed" into one row.
If you're curious about what COALESCE(t1.Name,t2.Name) does, it simply returns the first non-NULL value in its parameter list. Since t1.Name and t2.Name are the join columns for the FULL OUTER JOIN, they cannot both be NULL at the same time, right? And of course, if neither of them is NULL, it's okay to take t1.Name, because they'll be equal.