Professional SQL Server™ 2005 Programming

3.6. The UNION

Okay, enough with all the "old syntax" versus "new syntax" stuff — now we're into something that's the same regardless of what other join syntax you prefer — the UNION operator. UNION is a special operator we can use to cause two or more queries to generate one result set.

A UNION isn't really a JOIN, like the previous options we've been looking at — insteadit's more of an appending of the data from one query right onto the end of another query (functionally, it works a little differently from this, but this is the easiest way to look at the concept). Wherea JOIN combined information horizontally (adding more columns), a UNION combines data vertically (adding more rows), as illustrated in Figure 3-2.

Figure 3.2. Figure 3-2

When dealing with queries that use a UNION, there are just a few key points:

• All the UNIONed queries must have the same number of columns in the SELECT list. If your first query has three columns in the SELECT list, then the second (and any subsequent queries being UNIONed) must also have three columns. If the first has five, then the second must have five, too. Regardless of how many columns are in the first query, there must be the same number inthe subsequent query(ies).

• The headings returned for the combined result set will be taken only from the first of the queries. If your first query has a SELECT list that looks like SELECT Col1, Col2 ...