Sams Teach Yourself Transact-SQL in 21 Days, Second Edition

Using Union to Merge Result Sets

When you join tables, you get a result set where each row can contain values from several tables. In this last section, you will learn to merge two or more result sets into a single result set by using the UNION keyword.

Consider the problem of developing a mailing list of suppliers and customers. You would want to produce a list of names and addresses for each table. You don't need to join the tables; after all, there are no corresponding columns to join the tables. Figure 5.11 shows how rows from two separate queries are merged into a single result set.

Figure 5.11. UNION merges two result sets into a single result.

Here is the Customers and Suppliers mailing list query. Notice how the UNION statement connects ...