| |
 |
|
SQL Server
Tips by Burleson |
Set Operations
SQL is a language that is supposed to be based on sets. Dr. Codd
even defined the classic set operations as part of his eight basic
operators for a relational database. Yet we did not have a full
collection of basic set operations until the SQL-92 Standard.
By set operations, I mean union, intersection, and set difference --
the basic operators used in elementary set theory, which has been
taught in the United States public school systems for decades.
Perhaps the problem in SQL that you did not have in pure set theory
is that SQL tables are multisets (also called bags), which means
that, unlike sets, they allow duplicate elements (rows or tuples).
Dr. Codd's relational model is stricter and uses only true sets. SQL
handles these duplicate rows with an ALL or DISTINCT modifier in
different places in the language; ALL preserves duplicates and
DISTINCT removes them.
Another more subtle problem is that set operations only make sense
when the two sets are made up of the same kind of elements. In good
database model, each table has one and only one type of elements.
That is, you don't have more than one Inventory table, more than one
Personnel table, etc.
But when the INCITS H2 (nee ANSI X3) Database Standards Committee
added these operators, the model in the SQL-92 standard was to pair
off the two tables on a row-per-row basis for set operations.
(note: In SQL-92, we introduced the shorthand TABLE <table name> for
the query or subquery SELECT * FROM <table name>, which lets us
refer to a table as a whole without referring to its columns. I will
use this notation to save space)
This is a book excerpt from:
Advanced SQL Database Programmer Handbook
Donald K. Burleson, Joe Celko, John Paul Cook, Peter Gulutzan
ISBN: 0-9744355-2-X
http://www.rampant-books.com/ebook_dbazine_SQL_prog.htm
|
