Q
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

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.

This was last published in August 2005

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close