Introducing SQL Sets
Pages: 1, 2
SQL in a Nutshell
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]
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
SELECT EmployeeName AS Name, Address, ZIP FROM Employees
which would produce a table something like the following:
|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
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).
Why don't more people use
UNION queries? And what about
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.
Read more aboutSQL columns.
Return to ONLamp.com.