Introducing SQL Sets
Pages: 1, 2

			SQL in a Nutshell By Kevin Kline with Daniel Kline, Ph.D. January 2001 1-56592-744-3, Order Number: 7443 224 pages, $29.95

But what if this is an activity that you need to repeat frequently? Or what if the dataset is large enough that it is impractical to create a new database table? Or what if you simply don't have the permissions to do anything other than SELECT queries in your database? A UNION query is the answer!

The syntax for a UNION query is very straightforward:

query 1 UNION [ALL] query2 [ORDER BY sort_order]

The optional ALL keyword indicates the UNION should include all duplicates which would be ignored by default. So to create our table of addresses, we can use the following SQL statement

SELECT Contact AS Name, Address, ZIP FROM Suppliers
UNION
SELECT EmployeeName AS Name, Address, ZIP FROM Employees

which would produce a table something like the following:

This doesn't seem like such a big deal. But what if the data tables were somewhat more heterogeneous? One interesting thing about the UNION operator is that in general, any two queries can be joined as long as they have the same number of columns. Note that the data types don't have to be the same. That's pretty useful!

In the past, we've discussed using one-to-one tables to store specific data such as information about a music CD or a book that is linked to the table of common data about items in a store. If you instead chose to create a table of CDs and a table of books, you could create a SQL statement like the following that will generate a single result set consisting heterogeneous data:

SELECT CatalogID, Price, Description, PlayingTime AS Custom1, NumOfTracks AS Custom2, Artist AS Custom3, Label AS Custom4 FROM CDs
UNION
SELECT CatalogID, Price, Description, PageCount AS Custom1, Author AS Custom2, PublishDate AS Custom3, ISBN AS Custom4 FROM Books

In this scenario, not only do the columns in the new set contain values that mean different things, they can even contain different types of data (playing time is probably a time or text field, the page count is probably an integer).

Next steps

Why don't more people use UNION queries? And what about INTERSECT and EXCEPT? The truth is that these operations can be computationally intensive and there are often alternatives -- such as pulling out two data sets and using a C++ program to create the union, intersection, or difference set. But if your database supports them, they can occasionally be a lifesaver.

Starting in the next column, we're going to switch from discussing SQL for queries to discussing SQL statements for constructing tables and databases. That topic will occupy us for several columns and lead directly in to issues such as triggers, constraints, and all the myriad other tools that can save you time and effort. Until then, feel free to contact me with comments and questions.

John Paul Ashenfelter is president and CTO of TransitionPoint.com, a technology development and analysis group focusing on web database applications.

Read more aboutSQL columns.

Return to ONLamp.com.

John Paul Ashenfelter is president and CTO of TransitionPoint.com, a technology development and analysis group focusing on web database applications.

Ask John Paul your questions about the UNION operator and SQL sets in general.
You must be logged in to the O'Reilly Network to post a talkback.

Showing messages 1 through 5 of 5.

• Union queries
  2007-06-28 07:54:22  Andre726 [Reply | View]
  
  
  How can this be made to work in a many-to-many relationship? Do I have to introduce a third table with a unique key linked to the other 2 tables?? Any help with this question would be much appreciated.

• hello:
  2007-04-17 21:55:38  viswai [Reply | View]
  
  
  i am joining two tables using UNION ALL. The Datas I have joined should be stored in a new table. pls let me kow the Query. i am using Access 2000.
  
  thank you.

• Replacing Unions
  2002-07-12 08:47:38  karenjen [Reply | View]
  
  
  Hello Paul,
  
  I've just started a new job and I haven't used SQL in a very long time. We are currently using Crystal Reports 6.0 and are able to use the Union function. But, we are switching to Crystal Reports 8.0 which doesn't support Unions... what is the best function to replace the Union? Is it the INNER JOIN? Just curious... looking for a fast solution! Thanks, Karen

• Union
  2002-06-13 14:35:39  saumyak [Reply | View]
  
  
  How to use Union Operator in ASP embedded SQL!
  
  thnks

• Doubt in union operator
  2001-10-09 20:23:15  suresh175 [Reply | View]
  
  
  We are using UNION operator for getting the result from 2 sql statements. If one of the sql statements returns zero rows, then whole sql statement almost hangs, i.e. it takes more than 2hrs. If both the sql statement returns value it takes less that an minute. Could you please provide as the reason why it is so ?