This section discusses two ways in which select-queries may be used as components within a larger select-query. First we examine how to specify the operations of union, intersection, and difference in SQL. Next we look at basic subqueries in SQL. Let s begin with the union operation. The basic syntax is shown below. The reserved word union is allowed only between select-queries, not between table names.

<i class="emphasis">select-query</i><b class="bold">union</b> [<b class="bold">all</b>]<i class="emphasis">select-query</i>...

The select-queries must be union-compatible (i.e., same number of columns, and corresponding columns are based on compatible data types). By default, a set is returned that is the union of the tables returned from the individual select-queries, with any duplicate rows eliminated. To return a bag, specify all to include duplicate rows. Note that this is the opposite of the SQL select-list syntax, which returns a bag by default and uses distinct to remove duplicates. SQL:1999 allows the distinct option to be explicitly declared after union; if omitted, it is assumed by default. The explicit use of union distinct is not supported by SQL Server.

As an example, the query in Figure 11.50 lists each person who is an American or drives a Ford. The first select finds the Americans ( JimO , Lance ), the second select finds the Ford drivers ( Colleen , Lance ), and the union includes any person in either or both of these intermediate results. Although Lance appears in both of the tables to...