SQLite UNION vs. UNION ALL and Result Set Behavior

UNION with MAX() Returning Single Record Instead of Multiple

When working with SQLite, a common expectation is that a UNION operation between two SELECT statements will return a result set that combines all rows from both queries. However, this expectation can lead to confusion when using aggregate functions like MAX() in conjunction with UNION. Specifically, when the MAX() function is applied to columns with identical values across tables, the UNION operation may return fewer rows than anticipated. This behavior is not a bug but rather a direct consequence of how UNION is designed to work in SQLite.

Consider the following example, where two tables, A and B, are created and populated with identical values:

CREATE TABLE A (id1, id2);
CREATE TABLE B (id1, id2);
INSERT INTO A VALUES(100, 200);
INSERT INTO B VALUES(100, 200);

SELECT MAX(id1) AS na FROM A  
UNION  
SELECT MAX(id1) AS na FROM B;

In this case, the UNION operation returns only one row with the value 100, even though both SELECT statements individually return a row with the value 100. This outcome occurs because UNION eliminates duplicate rows by default. Since both SELECT MAX(id1) queries return the same value, the UNION operation deduplicates the result set, leaving only one row.

This behavior can be contrasted with cases where the MAX() values differ or when different columns are selected. For example:

-- Different MAX() values
INSERT INTO B VALUES(300, 400);
SELECT MAX(id1) FROM A
UNION
SELECT MAX(id1) FROM B;

-- Different columns
SELECT MAX(id1) FROM A
UNION
SELECT MAX(id2) FROM B;

In the first query, after inserting a new row into table B, the MAX(id1) values differ between the two tables (100 and 300), resulting in two distinct rows in the output. In the second query, selecting MAX(id1) from table A and MAX(id2) from table B also yields two distinct rows (100 and 200), as the values are inherently different.

The key takeaway is that UNION is designed to return a set of distinct rows. When the results of the individual SELECT statements are identical, the deduplication process inherent to UNION reduces the result set to a single row. This behavior is consistent across all versions of SQLite, from older versions like 3.2.1 to the latest releases.

Deduplication Mechanism in UNION and Its Impact on Aggregate Functions

The core issue arises from the deduplication mechanism embedded in the UNION operation. Unlike UNION ALL, which simply concatenates the result sets of the participating SELECT statements, UNION performs an additional step to remove duplicate rows. This deduplication is based on the entire row, meaning that all columns in the result set are compared to determine uniqueness.

When using aggregate functions like MAX(), the result set often consists of a single row. If the aggregated values are identical across multiple SELECT statements, the deduplication process will collapse these rows into one. This behavior is particularly noticeable when working with tables that have identical or highly similar data.

For example, consider the following query:

SELECT MAX(id1) FROM A
UNION
SELECT MAX(id1) FROM B;

If both tables A and B contain the same maximum value for id1, the UNION operation will return only one row. This outcome is consistent with SQLite’s design but can be counterintuitive for users expecting a row from each SELECT statement regardless of duplication.

The deduplication mechanism also has implications for performance. While UNION ensures a unique result set, it requires additional processing to compare and eliminate duplicate rows. In contrast, UNION ALL avoids this overhead, making it more efficient when duplicate rows are either impossible or irrelevant.

Resolving Unexpected Results with UNION ALL and Best Practices

To address the issue of unexpected deduplication, the UNION ALL operator can be used instead of UNION. Unlike UNION, UNION ALL does not perform deduplication, ensuring that all rows from the participating SELECT statements are included in the result set. For example:

SELECT MAX(id1) AS na FROM A  
UNION ALL  
SELECT MAX(id1) AS na FROM B;

In this case, the result set will always contain two rows, even if the MAX(id1) values are identical. This behavior aligns with the expectation that each SELECT statement contributes a row to the final result set.

When deciding between UNION and UNION ALL, consider the following best practices:

1. Use UNION ALL when duplicates are irrelevant or impossible: If the participating SELECT statements are guaranteed to return distinct rows, or if duplicates are acceptable, UNION ALL is the more efficient choice.

2. Use UNION when deduplication is required: If the result set must contain only unique rows, UNION is the appropriate operator. However, be aware of its behavior with aggregate functions and identical data.

3. Test with realistic data: Before deploying queries in production, test them with representative data to ensure that the results align with expectations. This step is particularly important when working with aggregate functions and set operations.

4. Document query behavior: When sharing queries with others or revisiting them after some time, document the intended behavior and any assumptions about the data. This practice helps prevent misunderstandings and ensures consistent results.

By understanding the differences between UNION and UNION ALL and their interactions with aggregate functions, developers can avoid common pitfalls and write more effective SQLite queries. Whether deduplication is desired or not, choosing the appropriate operator and testing thoroughly will lead to more predictable and reliable outcomes.