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:

Name	Address	ZIP
Sue Smith	100 South St.	12345
David Jones	2525 1st St.	12345
Troy Parker	1100 Main St.	23456
Claire Smith-Jones	400 East Main, Apt 5	56789

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.