The UNION Operator

UNION is an operator used to combine two or more SELECT statements and generate one result set. These SELECT statements must meet some conditions, such as the following:

• They must have the same number of columns. You can work around this restriction if you use constants in the SELECT statement with fewer columns, as shown in Listing 5.21.

Code Listing 5.21. Using a UNION Operator with Constants in the SELECT Statements

					
-- A constant has to be used in the second SELECT statement
-- because Shippers doesn't have a contactname column

USE Northwind
SELECT companyname, contactname FROM Suppliers WHERE country = 'USA'
UNION
SELECT companyname, 'N/A'FROM Shippers
GO

companyname                              contactname
---------------------------------------- ------------------------------
New Orleans Cajun Delights               Shelley Burke
Grandma Kelly's Homestead                Regina Murphy
Bigfoot Breweries                        Cheryl Saylor
New England Seafood Cannery              Robb Merchant
Speedy Express                           N/A
United Package                           N/A
Federal Shipping                         N/A

(7 row(s) affected)

• The column's data types must be compatible. In other words, the data types must be equivalent, can be converted implicitly, or must be explicitly converted using either CAST or CONVERT. In the previous example (Listing 5.21), the companyname column of both tables has the same data type (NVARCHAR), and the other column (contactname) is compatible with the 'N/A' constant.

The column names of the result set in a UNION operation are taken from the column names of the first SELECT statement of UNION. By default, UNION removes all duplicates from the result set. However, if you want to keep duplicates in the result set, use the ALL keyword when performing a UNION operation. Listing 5.22 shows the difference between using UNION and UNION ALL.

Code Listing 5.22. Using the ALL Option in UNION Operators

					
USE Northwind

SELECT city, country FROM Customers WHERE country = 'UK'
UNION
SELECT city, country FROM Suppliers WHERE country = 'UK'
SELECT city, country FROM Customers WHERE country = 'UK'
UNION ALL
SELECT city, country FROM Suppliers WHERE country = 'UK'
GO

city            country
--------------- ---------------
Cowes           UK
London          UK
Manchester      UK
(3 row(s) affected)

city            country
--------------- ---------------
London          UK
London          UK
London          UK
London          UK
Cowes           UK
London          UK
London          UK
London          UK
Manchester      UK

(9 row(s) affected)

The result set of a UNION operation can be ordered, but be aware that only one ORDER BY clause can be specified when using UNION, and it must be specified in the last SELECT statement. Listing 5.23demonstrates how to use ORDER BY in a UNION operation.

Code Listing 5.23. Using an ORDER BY in a UNION Operation

					
USE Northwind

SELECT city, country FROM Customers WHERE country = 'UK'
UNION ALL
SELECT city, country FROM Suppliers WHERE country = 'UK'
ORDER BY city
GO

city            country
--------------- ---------------
Cowes           UK
London          UK
London          UK
London          UK
London          UK
London          UK
London          UK
London          UK
Manchester      UK

(9 row(s) affected)

When using UNION, only the first SELECT statement can have an INTO keyword, which allows you to create a table on-the-fly with the result set of the UNION operation. Listing 5.24 creates a temporary table that stores the full name of employees and suppliers.

Code Listing 5.24. Creating a Table On-the-Fly with the Result of a UNION

					
USE Northwind

SELECT firstname + ''+ lastname as fullname
       INTO #employeesandsuppliers
FROM Employees
UNION
SELECT contactname FROM Suppliers WHERE country = 'usa'
ORDER BY fullname

SELECT * FROM #employeesandsuppliers
GO
(13 row(s) affected)

fullname
-------------------------------
Andrew Fuller
Anne Dodsworth
Cheryl Saylor
Janet Leverling
Laura Callahan
Margaret Peacock
Michael Suyama
Nancy Davolio
Regina Murphy
Robb Merchant
Robert King
Shelley Burke
Steven Buchanan

(13 row(s) affected)

What's Next?

You have been studying different ways to access data in databases. Until now, performance hasn't been an issue. However, as you might have experienced, databases are constantly growing, and sometimes this can hurt the performance of queries and applications that access the database. This performance degradation can turn out to be a very serious problem, because we want to be able to issue queries against the database and get the result set right away.

In general, indexes can be used to improve the performance of queries. The main characteristic of indexes is that they speed up data retrieval, even when working with big tables. In the next chapter, you go through all the steps and guidelines needed to create useful indexes that can improve the performance of queries and applications.