Manage Learn to apply best practices and optimize your operations.

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


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.

Dig Deeper on Oracle and SQL