BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

SQL Server Union Operations

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.

Using UNION ALL

There is a single clause that can be used to modify the behaviour of the UNION command. By adding the ALL clause, you instruct SQL Server that any duplicate rows in the combined results should be retained rather than excluded. This is ideal when you need to include the duplicated rows or when you know that every returned result will be unique. As the additional processing element is removed, the overall performance of the query can be substantially improved.

The query below matches the previous one except for the addition of the ALL clause. As the identical rows are retained in the results, this query returns six rows instead of the previous five.

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

Sorting Results

You may only have one ORDER BY clause in a SELECT statement. When using queries that include UNIONs, the ORDER BY clause must appear at the end of the T-SQL command. The columns being ordered can include the alias names applied to the first query. The sorting is applied to the entire, combined set of data, as in the following example:

SELECT
    CustomerNumber,
    Complaint AS Feedback,
    ComplaintTime AS Time,
    'Complaint' AS Type
FROM
    CustomerComplaints
UNION
SELECT
    CustomerNumber,
    Message,
    FeedbackTime,
    'Feedback'
FROM
    CustomerFeedback
ORDER BY
    Time
25 April 2009