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