Q
Problem solve Get help with specific problems with your technologies, process and projects.

Column names in a UNION

When doing a union on two databases where I create one big recordset using ASP, how do I then refer to the fields...

in the two databases if both databases are the same, but have different field names?

To start, I'm going to assume you meant to say tables instead of databases. The UNION operator works only on tables. If the tables exist in separate databases, then I believe you just qualify them, e.g. db1.tableA and db2.tableB.

The question you have is interesting, though, and the answer is that the result set assumes its column names from the first query. Consider the following example:

select albumname, artist, label
  from CDs
 where favourite = 'Y'
union all
select booktitle, author, publisher
  from books
 where choice = 'Y'

The result set will have columns named albumname, artist, and label. Often, this does not matter, since results sets are usually displayed by applications which provide meaningful column headings instead of using column names. For a query that combines CDs and books, the application would, one hopes, use column headings like "Name or title," "Artist or author," and "Label or publisher," while underneath the covers, so to speak, it would just refer to the column names of the first query.

Still, if you wish to change the column names, you can provide a column alias. This is especially important when the first query's select list contains expressions in addition to columns:

select label      as source
     , count(*)   as favourites
  from CDs
 where favourite = 'Y'
group by label
union all
select publisher
     , count(*)
  from books
 where choice = 'Y'
group by publisher

Finally, you asked "how do I then refer to the fields on the 2 databases?" and perhaps you have a need to distinguish which rows in the result set came from which table in the union. This requirement comes up fairly often, and the answer is simply to include an extra column in each query:

select 'CDs:   '  as sourcetype
     , label      as source
     , count(*)   as favourites
  from CDs
 where favourite = 'Y'
group by label
union all
select 'Books: '
     , publisher
     , count(*)
  from books
 where choice = 'Y'
group by publisher

Notice that each GROUP BY clause does not need to group on the first column, because it's the same for the entire (sub)set of results created by each query. Therefore, with no further logic required, it is obvious that this example should use UNION ALL instead of UNION, to avoid having the database sort all combined result rows looking for duplicate rows, since between the queries there cannot be any duplicates because of that first column. Without that first column, there could be duplicates between the queries, but ordinarily you'd want to use UNION ALL anyway, simply because in most unions, you really do want to see "duplicate" rows if they come from separate queries.


This was last published in October 2002

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.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close