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
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.