Microsoft® SQL Server® 2012 Bible

234

Part II: Building Databases and Working with Data

FIGURE 9-9

A union vertically appends the result of one SELECT statement to the result of another

SELECT statement.

Old Thing

Red Thing

New Thing

Blue Thing

Plane

Cycle

Train

Car

Table Two

Table One

Unions come in three basic fl avors: union, intersect union, and difference (or except) union.

Union [All]

The most common type of union by far is the UNION ALL query, which simply adds the

individual SELECT’s results.

In the following UNION query, the CustomerIDs from Customer2 and the CustomerIDs from

SalesOrder2 are appended together into a single list. The fi rst SELECT sets up the over-

all result, so it supplies the result set column headers. Each individual SELECT generates a

result set for the UNION operation, so each SELECT’s individual WHERE clause fi lters data

for that SELECT. The fi nal SELECT’s ORDER BY then serves as the ORDER BY for the entire

unioned results set. The ORDER BY must refer to the columns by either the fi rst SELECT’s

column names or by the ordinal position of the column:

SELECT CustomerID, 'Customer2 - Source' as Source

FROM dbo.Customer2

UNION ALL

SELECT CustomerID, 'SalesOrder2 - Source'

FROM dbo.SalesOrder2

ORDER BY 1;

The resulting record set uses the column names from the fi rst SELECT statement:

CustomerID Source

----------- --------------------

101 Customer2 - Source

102 Customer2 - Source

102 SalesOrder2 - Source

c09.indd 234c09.indd 234 7/30/2012 4:25:09 PM7/30/2012 4:25:09 PM

http://www.it-ebooks.info