| |
 |
|
SQL Server
Tips by Burleson |
Set Operations: Union
Microsoft introduced its ACCESS database product in 1992, after five
years and tens of millions of dollars' worth of development work.
The first complaints they got on their CompuServe user support forum
involved the lack of a UNION operator. UNIONs are supported in
SQL-86, SQL-89, and SQL-92, but the other set operations have to be
constructed by the programmer in SQL-89. The syntax for the UNION
statement is:
<query> UNION [ALL] <query>
Technically, this BNF is not right, but I will get back to that
later. The UNION statement takes two tables and builds a new table
from them. The two tables must be "union compatible", which means
that they have the same number of columns, and that each column in
the first table has the same datatype (or automatically cast to it)
as the column in the same position in the second table.
That is, their rows have the same structure, so they can be put in
the same final result table. Most implementations will do some
datatype conversions to create the result table, but this is very
implementation-dependent and you should check it out for yourself.
What is interesting is that the result of a UNION has no name, and
its columns are not named. If you want to have names, then you have
to use an AS operator to create those names, thus.
((SELECT a, b, c FROM TableA WHERE
city = 'Boston')
UNION
(SELECT x, y, z FROM TableB WHERE city = 'New York'))
AS Cities (tom, dick, harry)
However, in actual products will find a multitude of other ways of
doing this:
-
The columns have the names of the first
table in the UNION statement.
-
The columns have the names of the last
table in the UNION statement.
-
The columns have the names generated by
the SQL engine.
-
The columns are referenced by a position
number. This was the SQL-89 convention.
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
|
