Comprehensive Guide to Using ORDER BY with UNION ALL in SQL Server

Introduction

In SQL Server database development, the UNION ALL operator is commonly used to combine multiple query result sets, while the ORDER BY clause serves to sort results. However, developers often encounter syntax errors when attempting to use ORDER BY within subqueries of UNION ALL operations. This paper examines the technical foundations of this issue and presents multiple effective solutions.

Problem Background and Error Analysis

Many developers initially attempt the following query structure:

SELECT * FROM (SELECT * FROM TABLE_A ORDER BY COLUMN_1) DUMMY_TABLE
UNION ALL 
SELECT * FROM TABLE_B

This approach causes SQL Server to generate the error: "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."

The core reason for this error lies in SQL Server's query optimizer processing logic. In contexts such as derived tables and subqueries, the ORDER BY clause is considered meaningless without accompanying TOP or FOR XML clauses, as sorting of intermediate results may not be preserved in the final result set.

Primary Solution: Adding Sort Columns

The most direct and effective solution involves adding custom sort columns to each part of the UNION ALL operation, then performing unified sorting in the outer query:

SELECT * 
FROM (
    SELECT *, 1 AS sortby FROM TABLE_A 
    UNION ALL 
    SELECT *, 2 AS sortby FROM TABLE_B
) combined_data
ORDER BY sortby, COLUMN_1

This method works by:

• Using the sortby column to identify data sources, ensuring TABLE_A records appear before TABLE_B records
• Enabling primary sorting by source in the outer ORDER BY, followed by secondary sorting on other columns
• Maintaining the UNION ALL characteristic of not removing duplicate records

Simplified Syntax and Multi-Column Sorting

In practice, unnecessary parentheses can be omitted for more concise query writing:

SELECT *, 1 AS RN FROM TABLE_A
UNION ALL 
SELECT *, 2 AS RN FROM TABLE_B
ORDER BY RN, COLUMN_1, COLUMN_2

Advantages of this approach include:

• More concise and readable code
• Support for multi-column sorting, providing flexible sorting control
• Comparable execution efficiency to nested approaches

Alternative Approach: Using TOP Clause for Section Sorting

In specific scenarios requiring maintained internal sorting order within individual query parts, the TOP clause can be used with ORDER BY:

SELECT pass1.* FROM 
 (SELECT TOP 1000 tblA.ID, tblA.CustomerName 
  FROM TABLE_A AS tblA ORDER BY CustomerName) AS pass1
UNION ALL 
SELECT pass2.* FROM 
  (SELECT TOP 1000 tblB.ID, tblB.CustomerName 
   FROM TABLE_B AS tblB ORDER BY CustomerName) AS pass2

Important considerations for this method:

• The TOP value must be sufficiently large to include all relevant records
• Suitable for scenarios requiring specific internal sorting within sections
• Should be used cautiously in performance-critical situations

Practical Application Case Study

The sales reporting scenario from the reference article effectively demonstrates practical applications of UNION ALL with sorting. In this case, generating reports containing sales records of different statuses requires:

SELECT ProdId, Amount, 1 AS section_order FROM Sales WHERE Status = 0
UNION ALL
SELECT 0, SUM(Amount), 2 FROM Sales WHERE Status = 0
UNION ALL  
SELECT ProdId, Amount, 3 FROM Sales WHERE Status = 1
UNION ALL
SELECT 0, SUM(Amount), 4 FROM Sales WHERE Status = 1
ORDER BY section_order, ProdId

This structure ensures:

• Status 0 detail records appear first
• Followed by Status 0 summary rows
• Then Status 1 detail records
• Finally Status 1 summary rows
• Internal sorting by product ID within each section

Performance Considerations and Best Practices

When using UNION ALL with sorting, consider the following performance factors:

• Avoid complex sorting operations on large datasets when possible
• Consider creating appropriate indexes on sort columns
• For fixed sorting requirements, use persisted computed columns
• Employ parameterized queries in stored procedures for improved reusability

Conclusion

The approach of adding custom sort columns effectively resolves the challenge of combining UNION ALL with ORDER BY. This method not only provides syntactically correct solutions but also offers flexible sorting control capabilities. In practical development, developers should select appropriate solutions based on specific requirements while carefully considering performance implications. Understanding SQL Server's query processing mechanisms enables the creation of more efficient and reliable database queries.