Introducing SQL Sets
08/02/2001SQL has many language elements that are not commonly introduced in quick surveys of the language. Because SQL is based on relational algebra, it should come as no surprise that the standard contains several set-oriented operators.
In the interest of completeness, and to provide a tool that is extremely useful in dealing with heterogeneous data, it's time we take a look at the UNION operator.
SQL and sets
If you were to dredge up the memories of middle school, you'll probably remember vague notions of learning about sets. If you have ever studied logic, you may have even worked with Venn diagrams.
Sets are simply groups of discrete elements. In the strict mathematical world, each set is made up of unique numerical elements; in the SQL world, a set is a collection of rows of data that may or may not contain duplicates. You've actually been working with SQL sets for quite a while -- queries are simply a set (strictly speaking, a subset) of a database table.
There are three simple ways to compare sets:
- creating a union of two (or more) sets;
- generating the intersection between two sets; and
- finding the exceptions (or differences) between two sets.
While all of these functions are defined in the SQL standard (at least in the SQL-92 standard), the exact implementation varies widely by vendor. Many database engines leave out some or all of these functions because they are not used as frequently as other commands. Or vendors may omit them because the function of the operators can be recreated using SQL and/or the database engine's programming language. In rough order of implementation, most databases allow the UNION operator, some provide the INTERSECT operator, and a few provide the functionality of the EXCEPT operator using a different operator name (for example, Oracle's MINUS operator).
|
Also in aboutSQL: |
Using SQL sets
I'm sure that some of you may be wondering why I dedicated a column to this dusty corner of the SQL world. Fear not! There's a very good reason to learn about SQL sets, particularly the UNION operator.
Working with sets provides the flexibility to manipulate two tables that otherwise have no relationship -- where joins are not possible or useful. Let's start with an simple example. You have a database with a table of employees, and a table of suppliers.
Employees |
|||
| EmployeeID | EmployeeName | Address | ZIP |
| 61 | Sue Smith | 100 South St. | 12345 |
| 62 | David Jones | 2525 1st St. | 12345 |
Suppliers |
|||
| SupplierID | Contact | Address | ZIP |
| A1-674 | Troy Parker | 1100 Main St. | 23456 |
| ZZ-1A1 | Claire Smith-Jones | 400 East Main, Apt 5 | 56789 |
If you were sending Christmas cards to all suppliers and employees, you could certainly use a single table of name, address, and ZIP code data that could be the data source for a mail merge. Of course, you could create a new table, execute two SQL statements to insert the appropriate columns, and call it a day.
Pages: 1, 2 |
