|
At first glance, the UNION operator seems redundant. After
all, any statement of the form
SELECT x,y,z FROM t WHERE Condition1
UNION
SELECT x,y,z FROM t WHERE Condition2
is equivalent to
SELECT x,y,z FROM t WHERE Condition1 OR Condition2
I *could* think of a use for a query that goes
SELECT x,y,z FROM t1 WHERE Condition1
UNION ALL
SELECT x,y,z FROM t2 WHERE Condition2
But then I’d probably have some normalization issues to
think about, since there are not many cases when I’d need
to store the same type of data in two different tables.
However, I recently came across a use of the UNION operator
that makes perfect sense:
Quite often one relates data to intervals of time in
structures such as the following:
lTimeID int identity primary key
wStart datetime
wStop datetime
lEmployeeID int
lTaskID int
A UNION query can be used to produce output such as the
following:
01/Jan/2001 10:00 Bob Started Task A
01/Jan/2001 10:30 Mary Started Task B
01/Jan/2001 10:35 Bob Stopped Task A
01/Jan/2001 10:37 Bob Started Task C
01/Jan/2001 10:45 Mary Stopped Task B
Suppose our time intervals are stored in a table called
tblTime, and our employee and task names are stored in
tblEmployee and tblTask.
The output above can be generated with a statement like
SELECT wStart AS w, tblEmployee.sEmployee,
'Started '+tblTask.sTask
FROM tblTime
UNION ALL
SELECT wStop AS w, tblEmployee.sEmployee,
'Stopped '+tblTask.sTask
FROM (tblTime
INNER JOIN tblEmployee
ON tblTime.lEmployeeID=tblEmployee.lEmployeeID)
INNER JOIN tblTask ON tblTime.lTaskID=tblTime.lTaskID
ORDER BY wStart
Note that the ORDER BY clause refers to the name of a field
which is only selected in the first part of the query.
Both MS SQL Server and MS-Access require that fields in the
ORDER BY clause are specified this way, but all of the
records in the output will be sorted.
|