Microsoft® SQL Server® 2012 Bible

236

Part II: Building Databases and Working with Data

Intersection union queries are useful to prove that two queries give the same results. When all three queries have the

same result count, the two queries must be functionally equivalent.

Query A gives 1234 rows.

Query B gives 1234 rows.

Query A intersect Query B gives 1234 rows.

Difference Union/Except

The difference union is the union equivalent of the set difference query — it fi nd rows in

one data source that are not in the other data source.

SQL Server uses the ANSI Standard keyword EXCEPT to execute a difference union:

SELECT CustomerID

FROM dbo.Customer2

EXCEPT

SELECT CustomerID

FROM dbo.SalesOrder2

ORDER BY 1;

Result:

CustomerID

-----------

101

103

Whereas a set difference query is interested only in the join conditions (typically the pri-

mary and foreign keys) and joins the rows horizontally, a difference union EXCEPT query

looks at the entire row (or more specifi cally, all the columns that participate in the union’s

SELECT statements).

Now that you know how to combine sets of data, it’s time to introduce subqueries into sets.

Subqueries come in many different fl avors, and while they all have their strengths and

weaknesses, none perform well 100 percent of the time. It’s important to not only know

the different types of subqueries, but also to frequently check to make sure the subquery

you picked originally is still the best choice as you increase the scope of what your query

is doing. Just because a nested subquery starts out running just as quickly as a CTE for

a given query, doesn’t mean that it will continue to outperform that CTE as the logic and

aggregates become more complex.

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

http://www.it-ebooks.info