Learning SQL on SQL Server 2005

Chapter 7. Set Operations

In Chapter 4, we looked at how data can be retrieved from multiple tables using joins. In this chapter, we discuss how data can also be retrieved from multiple tables by using set operations . We look at the set operations available in SQL Server 2005. Because not all the SQL set operations are explicitly available in SQL Server 2005, we will also look at the IN predicate and its negation, NOT..IN, which are ways around the explicit set operations. In the final section of this chapter, we look at the UNION operation in relation to the join operation, and how the UNION operation can be used to get the results of some joins.

Introducing Set Operations

A set is a collection of objects. In relational databases, a table can be regarded as a set of rows. Elements in a set do not have to be ordered. In relational databases, rows do not have to be ordered as they are entered or stored. Set operations are used in SQL to retrieve data from multiple sets, and include a binary union, binary intersection and binary set difference . A result set is obtained in SQL from the result of a SELECT.

A binary union is a set operation on two sets, the result of which contains all the elements of both sets. A binary intersection generates values in common between two sets. And, a binary set difference generates values in one set less those contained in another set.

Three explicit set operations are used in SQL: UNION, INTERSECT, and MINUS (for set difference). SQL Server 2005 ...