Using UNION with GROUP BY in T-SQL: Core Concepts and Practical Guidelines

Introduction and Problem Context

In T-SQL query development, the UNION operator is commonly used to combine result sets from multiple SELECT statements, while the GROUP BY clause is employed for data aggregation and grouping. When developers attempt to integrate these two, they often encounter syntactic or logical confusion, such as how to correctly reference specific columns in unioned results. This analysis is based on a typical scenario: a user wishes to group by the first column of a UNION result, but the initial query fails due to improper column referencing. The original query example is as follows:

SELECT  *
FROM    ( SELECT    a.id ,
                    a.time
          FROM      dbo.a
          UNION
          SELECT    b.id ,
                    b.time
          FROM      dbo.b
        )
GROUP BY 1

This query attempts to use GROUP BY 1 to group by the first column (i.e., id), but this is not supported in T-SQL because the GROUP BY clause typically requires explicit column names or expressions, not ordinal positions. Ordinal references are only partially supported in the ORDER BY clause and are discouraged even there, as they depend on the order of the SELECT list and can introduce errors during query modifications.

Automatic Deduplication in UNION and Grouping Needs

One of the core features of the UNION operator is its automatic removal of duplicate rows, meaning that when merging result sets, the system implicitly performs a duplicate check based on all columns. For example, in the following query:

SELECT a.id,
       a.time
  FROM dbo.TABLE_A a
UNION
SELECT b.id,
       b.time
  FROM dbo.TABLE_B b

If identical combinations of id and time exist in both TABLE_A and TABLE_B, UNION will return only one row, eliminating the need for additional grouping. This behavior stems from set theory's union operation, ensuring the uniqueness of the result set. Therefore, in most scenarios using UNION, an explicit GROUP BY clause becomes redundant, unless the developer requires aggregation based on specific columns (e.g., using COUNT or SUM).

In contrast, the UNION ALL operator retains all rows, including duplicates, offering higher performance by avoiding deduplication overhead. If using UNION ALL and duplicates need to be removed, GROUP BY should be applied. For instance:

Select Z.id
From    (
        Select id, time
        From dbo.tablea
        Union All
        Select id, time
        From dbo.tableb
        ) As Z
Group By Z.id

In this example, the subquery is aliased as Z, allowing the outer query to correctly reference Z.id for grouping. This approach ensures code clarity and maintainability, avoiding the pitfalls of ordinal column references.

Best Practices for Column Referencing and Code Examples

In T-SQL, referencing columns in unioned results should adhere to explicit naming principles. Best practices include:

1. Assigning aliases to subqueries or derived tables to reference specific columns in outer queries. For example, after aliasing the union result as Z, use GROUP BY Z.id.
2. Avoiding ordinal column references (e.g., GROUP BY 1), as they are not supported by GROUP BY and can lead to fragile code—changes in the SELECT list order may inadvertently alter query logic.
3. Ensuring that the GROUP BY clause includes all non-aggregated columns when aggregation is needed, unless using extensions specific to databases like MySQL (which T-SQL does not support).

Below is a standardized code example demonstrating how to efficiently combine UNION ALL with GROUP BY:

-- Using UNION ALL to merge tables and group to remove duplicates
SELECT id, 
       MAX(time) AS latest_time  -- Example aggregate function
FROM (
    SELECT id, time
    FROM dbo.table_a
    UNION ALL
    SELECT id, time
    FROM dbo.table_b
) AS combined_data
GROUP BY id
ORDER BY id;

This query first uses UNION ALL to quickly merge all rows from two tables, then removes duplicate id values via GROUP BY id, calculating the latest time for each id. By using the alias combined_data, column references become explicit and safe.

Performance Considerations and Extended Discussion

In practical applications, choosing between UNION and UNION ALL involves balancing performance and data accuracy. The deduplication process in UNION may increase query overhead, especially with large datasets; whereas UNION ALL is more efficient but requires subsequent handling of duplicates. For example, in log analysis or real-time monitoring scenarios, combining UNION ALL with GROUP BY can optimize throughput.

Additionally, developers should note differences between T-SQL and other SQL dialects, such as MySQL. MySQL allows omitting non-aggregated columns in GROUP BY, but this causes errors in T-SQL. Using explicit column references uniformly enhances cross-platform code compatibility.

In summary, understanding the implicit deduplication feature of UNION is key to avoiding unnecessary grouping. By adopting aliasing and explicit column referencing, developers can write efficient and maintainable T-SQL queries, thereby improving overall database operation quality.