UNION vs. UNION ALL: The Difference That Costs Performance

1. What UNION and UNION ALL have in common

Both UNION and UNION ALL combine the result sets of two or more SELECT statements vertically into a single output, unlike a JOIN, which links rows horizontally over shared columns. Both operators require that all participating SELECT statements return the same number of columns with compatible data types, and the output's column names come from the first query in the chain.

The decisive difference lies in how duplicates are handled: UNION automatically removes all rows that match exactly, while UNION ALL keeps every row from every participating query unchanged, even if the same row occurs multiple times. This seemingly small syntactic difference, a single extra keyword, has a direct impact on the execution plan and thus on the performance of every query that combines result sets.

Both operators come from relational algebra, where a table is understood as a set of rows, and a true mathematical union by definition contains no duplicates. UNION implements that mathematical definition faithfully. UNION ALL is the pragmatic SQL extension that trades away that purity for performance, because in practice most combined result sets do not contain real overlaps anyway, and deduplication would just be extra work. Anyone who understands the difference between the two operators makes the choice between UNION and UNION ALL deliberately, not out of habit.

2. How UNION deduplicates internally

To detect duplicates, UNION must be able to compare every row of the combined result set against every other row. Most databases solve this with an implicit sort or hash step: either all rows are sorted so that identical rows end up directly next to each other and can be removed in a single pass, or a hash table is built where every row is checked against already seen rows via a hash value. Both strategies cost extra memory and extra CPU time compared to a plain concatenation of the result sets.

On small result sets with a few hundred rows this extra overhead is barely measurable. On large queries with several million rows, however, the sort or hash step becomes a distinct, sometimes dominant cost factor in the execution plan, especially when the data to be sorted no longer fits entirely in memory and the database has to spill to temporary disk storage for the sort operation.

-- UNION deduplicates: identical rows from both queries appear once
SELECT customer_id, email FROM newsletter_subscribers
UNION
SELECT customer_id, email FROM customers WHERE opted_in = true;

-- UNION ALL keeps every row, including exact duplicates
SELECT customer_id, email FROM newsletter_subscribers
UNION ALL
SELECT customer_id, email FROM customers WHERE opted_in = true;

3. The real cost in the execution plan

A look at EXPLAIN ANALYZE makes the difference concretely visible. With UNION, an extra node appears in the plan in addition to the two subqueries, typically HashAggregate or Unique after a Sort in PostgreSQL, a Distinct Sort operator in SQL Server. This node processes the entire combined result set once more before the final result goes to the client. With UNION ALL, this node is missing entirely, the two partial results are appended directly and passed through.

In practice this means: a query combining two million rows from table A with one million rows from table B has to sort or hash three million rows with UNION, just to find out in the end that there are no duplicates at all. That work is pure waste when the two source sets can never contain the same row from a business logic perspective, for example because they differ by disjoint primary key ranges or different source systems.

A further, often overlooked cost factor concerns memory consumption during execution. UNION's hash aggregate or sort step temporarily holds the entire combined result set in the database's working memory before the result can be returned to the client. With UNION ALL, on the other hand, the database can frequently process the results as a stream and pass them through row by row without waiting for the full result set. This difference in the execution model matters especially for queries that get processed further in an outer query.

4. When UNION ALL is guaranteed safe

UNION ALL is always the correct and faster choice whenever it is established that the combined result sets cannot contain real duplicates, or when duplicates are actually desired for business reasons, for example before an aggregation downstream. The most common safe case: the two subqueries filter the same table by mutually exclusive conditions, for example WHERE status = 'active' in one and WHERE status = 'inactive' in the other subquery. Since a record cannot have both statuses at once, an overlap is logically excluded.

A second safe case exists when the subqueries combine different source tables with disjoint primary keys, for example an archive table and an active table between which a record, through a migration process, uniquely exists in only one of the two. In both cases, UNION ALL returns exactly the same result as UNION, but without the unnecessary deduplication step, which means a noticeable performance gain on large data volumes.

-- Safe UNION ALL: mutually exclusive status filters on the same table
SELECT order_id, customer_id, 'active' AS bucket
FROM orders
WHERE status IN ('pending', 'processing')

UNION ALL

SELECT order_id, customer_id, 'closed' AS bucket
FROM orders
WHERE status IN ('shipped', 'cancelled');

-- Safe UNION ALL: disjoint source tables by design
SELECT order_id, amount, 'current_year' AS source
FROM orders
UNION ALL
SELECT order_id, amount, 'archive' AS source
FROM orders_archive;

5. Combining result sets from different tables

A common use case for UNION or UNION ALL is merging business-related but structurally different tables into a unified output, for example a list of all touchpoints of a customer coming from separate tables for emails, calls, and support tickets. Each subquery brings its own columns, but they must be normalized to a common shape with an identical column count and compatible types, often with an additional constant column marking the origin of the row.

This marker column, called bucket or source in the previous code example, is almost always worthwhile in practice when rows from different tables are combined. Without it, the result no longer reveals which source a given row came from, which becomes particularly problematic during debugging or downstream filter operations on the combined result.

-- Combining structurally different tables into one unified feed
SELECT customer_id, 'email' AS channel, sent_at AS event_time, subject AS detail
FROM email_log
UNION ALL
SELECT customer_id, 'call' AS channel, called_at AS event_time, notes AS detail
FROM call_log
UNION ALL
SELECT customer_id, 'ticket' AS channel, created_at AS event_time, title AS detail
FROM support_tickets
ORDER BY customer_id, event_time DESC;

6. Rules for column count and data types

Every SELECT statement participating in UNION or UNION ALL must return exactly the same number of columns in the same order. A differing column count causes a syntax error while the statement is parsed in every common database, not only at runtime. The data types of columns at the same position do not need to be identical, but must be implicitly convertible into each other, for example an INTEGER and a NUMERIC at the same position, which the database automatically widens to the more general type.

The database takes the column names of the combined output exclusively from the first SELECT statement in the chain, aliases in later subqueries are ignored. Anyone who wants readable output should therefore assign consistent aliases already in the first subquery. With strongly differing data types between subqueries, an explicit CAST is recommended instead of relying on the database's implicit conversion, since that can differ between database systems.

A common pitfall arises when one subquery returns a NULL constant at a position where another subquery supplies a concrete type, for example a string. Some databases derive the column's target type from the first non-NULL subquery, others require an explicit type annotation right on the NULL literal, for example NULL::text in PostgreSQL or CAST(NULL AS VARCHAR(255)) portably across several systems. Without this precision, unexpected type conflicts or an overly wide target type can occur, slowing down downstream comparisons.

-- Explicit CAST avoids relying on implicit type coercion across databases
SELECT customer_id, first_name AS label, 'customer' AS row_type
FROM customers
UNION ALL
SELECT lead_id, CAST(NULL AS VARCHAR(255)) AS label, 'lead' AS row_type
FROM leads
WHERE converted_at IS NULL;

-- Mismatched column count fails at parse time, not at runtime
-- SELECT id, name FROM table_a
-- UNION ALL
-- SELECT id, name, extra_column FROM table_b  -- syntax error

7. Ordering and LIMIT on combined queries

An ORDER BY may only appear once in a UNION or UNION ALL chain, right at the end of the entire statement, and then sorts the whole combined result, not the individual subqueries. An ORDER BY inside a single subquery is only allowed in standard SQL together with LIMIT or FETCH FIRST on that exact subquery, to cap its rows before the combination, otherwise the sort order is not guaranteed to survive the combination anyway.

The same applies to LIMIT: a LIMIT at the end of the entire statement caps the combined final result after sorting, while a LIMIT inside a subquery, wrapped in parentheses, only caps the row count of that one subquery before the combination. This distinction is often confused in practice, leading to queries that return either too many or unexpectedly few rows.

8. Distinguishing from INTERSECT and EXCEPT

Besides UNION and UNION ALL, standard SQL knows two further set-based operators with related syntax but different semantics: INTERSECT returns only the rows that occur in both partial results, and EXCEPT, called MINUS in Oracle, returns only the rows from the first subquery that do not occur in the second. Both operators deduplicate by default like UNION, and both also exist analogously with the ALL suffix, which suppresses deduplication.

The mistake of using UNION for a filter operation between two tables when INTERSECT or EXCEPT was actually intended is not uncommon in practice. Anyone who wants to check which customers appear in both table A and table B needs INTERSECT, not UNION, which instead returns all customers from both tables together, a completely different business result.

-- INTERSECT: customers present in both the CRM export and the newsletter list
SELECT email FROM crm_export
INTERSECT
SELECT email FROM newsletter_subscribers;

-- EXCEPT (MINUS in Oracle): customers in the CRM export but not subscribed
SELECT email FROM crm_export
EXCEPT
SELECT email FROM newsletter_subscribers;

-- Using UNION here would be a mistake: it merges both lists instead of
-- comparing them, producing a completely different, incorrect result

9. Common mistakes in practice

The most common mistake is reflexively using UNION as a supposedly safe default, without checking whether the combined sets can even contain duplicates in the first place. In codebases that have grown historically, dozens of UNION queries are often found where nobody can explain anymore why deduplication was originally used, even though a brief business analysis shows that UNION ALL would deliver the same correct result.

A second mistake is assuming UNION ALL is always correct from a business perspective just because it is faster. If the source data can actually contain duplicates and those are undesired from a business standpoint, for example an email distribution list fed from multiple sources, UNION ALL leads to duplicate sends. The right decision always requires a business review of the data situation, not a blanket rule in one direction or the other.

10. Summary

The difference between UNION and UNION ALL looks small at first glance, a single keyword, but the impact on performance is considerable on large result sets. UNION deduplicates with an implicit sort or hash step over the entire combined result set, UNION ALL appends the partial results directly without that step. Anyone who knows the combined sets cannot contain real duplicates should always use UNION ALL.

The choice between the two operators is not purely a performance question, it requires a business review of the data situation. Mutually exclusive status filters on the same table and structurally separated source tables are the most common safe cases for UNION ALL. For real set operations like finding common or exclusive rows, INTERSECT and EXCEPT are the more fitting, semantically clearer tools than a misused UNION query.

11. FAQ: UNION vs. UNION ALL