Microsoft SQL Server 2012 Bible

Using Unions

The UNION operation is different from a join. In relational algebra terms, a union is addition, whereas a join is multiplication. Instead of extending a row horizontally as a join would, the union stacks multiple result sets into a single long table, as shown in Figure 9.9.

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

Unions come in three basic flavors: 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 first SELECT sets up the overall 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 filters data for that SELECT. The final 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 first 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 ...