SQL 2005+

SQL Server Union Operations

by Richard Carr, published at http://www.blackwasp.co.uk/SQLUnion.aspx

The twenty-fifth part of the SQL Server Programming Fundamentals tutorial takes a step back from creating database schemas and procedures and returns to queries. In this article we consider the use of the UNION keyword in Transact-SQL.

Union Operations

In this instalment of the SQL Server Programming Fundamentals tutorial we will change direction somewhat. The previous articles have been focussed on creating schema elements in a SQL Server database. This article moves the focus back to interacting with the data with the use of queries. This marks the start of a group of articles that examine further querying options, the use of operators and functions and more SQL Server programming elements for use in stored procedures, views and triggers. Later in the tutorial we will return to schema elements when we examine triggers, by which time we will have a general understanding of the various areas of Transact-SQL (T-SQL) that are useful when creating them.

This article describes the use of union operations using the UNION command. A union allows you to combine the results of two queries. The results of the second query are simply appended to those of the first. If you wish to combine the rows from three or more queries, you can chain two or more UNION commands. The source of the data in each query is unimportant. You can combine information from two different tables or perform two queries against the same table, potentially using different columns for each select. The key limitations are that the two queries must return the same number of columns and that the columns must have compatible data types that appear in the same order.

JoBS Database

This article's examples can be executed against the JoBS tutorial database. The database has been created throughout the course of the tutorial. You should create the JoBS database by downloading and executing the script at the top of this page. If you already have a working JoBS system you should replace it using the downloadable script. The new version includes an extra table containing customer feedback that we will use to demonstrate the UNION command.

Performing Union Queries

In the first example we will look at the basic UNION command. This command combines the results of two queries into a single set of data. It then looks through the data to find any exact duplicates and removes them before returning the results.

We will use two queries for the demonstration. The first query will list all of the complaints from the CustomerComplaints table. Run the statement below to see the three rows of data.

SELECT
    CustomerNumber,
    Complaint,
    ComplaintTime
FROM
    CustomerComplaints

The new CustomerFeedback table stores customer comments that are not formal complaints. To see the three rows that exist in the table, execute the following statement. Note that we are using columns with compatible data types to those in the previous query, as will be required for the UNION.

NB: These are simple queries so that the UNION is not over-complicated. In normal use you can include queries containing WHERE clauses.

SELECT
    CustomerNumber,
    Message,
    FeedbackTime
FROM
    CustomerFeedback

If you compare the results of the two queries you should notice that one row in each query is an exact duplicate. This will be removed when we execute the query containing a union of both of these sets of data.

The UNION command is used by placing it directly between two SELECT statements. We can therefore combine the results of the previous two queries by executing the following statement. This will append the results of the second query to those of the first, remove one of the two duplicates and return a result set containing five rows.

SELECT
    CustomerNumber,
    Complaint,
    ComplaintTime
FROM
    CustomerComplaints
UNION
SELECT
    CustomerNumber,
    Message,
    FeedbackTime
FROM
    CustomerFeedback

The column names in the two individual queries do not match. When you view the results you should notice that the column names are taken from the first query. If you wish to use column aliases, you may apply them to the first query only for the correct results. For example:

SELECT
    CustomerNumber,
    Complaint AS Feedback,
    ComplaintTime AS Time
FROM
    CustomerComplaints
UNION
SELECT
    CustomerNumber,
    Message,
    FeedbackTime
FROM
    CustomerFeedback