235
Chapter 9: Merging Data with Joins, Subqueries, and CTEs
9
CustomerID Source
----------- --------------------
103 Customer2 - Source
104 SalesOrder2 - Source
105 SalesOrder2 - Source
When constructing unions, you need to understand a few rules:■ (^) UNION returns a distinct list set of values after the included result sets are com-
bined. UNION ALL returns all rows from each result set, regardless of whether
duplicates exist.
■ (^) Every SELECT must have the same number of columns, and each column must have
a compatible data type with the corresponding columns in the other queries.
■ (^) The column names, or aliases, are determined by the fi rst SELECT.
■ (^) The ORDER BY clause sorts the results of all the SELECTs and must go on the last
SELECT, but it uses the column names from the fi rst SELECT.
■ (^) Expressions may be added to the SELECT statements to identify the source of the
row so long as the column is added to every SELECT.
■ (^) The union may be used as part of a SELECT into (a form of the insert verb cov-
ered in Chapter 12, “Modifying Data in SQL Server”), but the INTO keyword must go
in the fi rst SELECT statement.
Unions aren’t limited to two tables. As long as the total number of tables referenced by a
query is 256 or fewer, SQL Server handles the load.
Intersection Union
An intersection union fi nds the rows common to both data sets. An inner join fi nds common
rows horizontally, whereas an intersection union fi nds common rows vertically.
SELECT CustomerID
FROM dbo.Customer2
INTERSECT
SELECT CustomerID
FROM dbo.SalesOrder2
ORDER BY 1;
Result:
CustomerID
102
An intersection union query is similar to an inner join. The inner join merges the rows hor-
izontally, whereas the intersect union stacks the rows vertically. The intersect must match
every column to be included in the result. A twist, however, is that the intersect sees null
values as equal and accepts the rows with nulls.
c09.indd 235c09.indd 235 7/30/2012 4:25:09 PM7/30/2012 4:25:09 PM
http://www.it-ebooks.info