UNION vs UNION ALL in SQL: Key Differences

When combining datasets in SQL, choosing between UNION and UNION ALL is one of the most common decisions an analyst makes. It is also a classic SQL technical interview question because it directly tests whether you understand execution cost, query plans, and data grain.

While both operators vertically stack rows from multiple SELECT statements, their performance implications on large datasets can mean the difference between a query finishing in 200 milliseconds versus timing out on disk.

For related query optimization strategies, check out our SQL JOIN Fan-Out Guide and our hands-on Data Analyst Career Track.

1. Syntax and Structural Rules

Before examining performance, both operators follow identical structural constraints:

1. Equal Column Count: Each SELECT statement must return the exact same number of columns.
2. Compatible Data Types: Corresponding columns must have compatible data types in positional order (e.g., Column 1 in query A must match the type of Column 1 in query B).
3. Column Names Inherited from Query 1: The column names in the final output are determined solely by the first SELECT statement.
4. Single ORDER BY Clause: You cannot put an ORDER BY clause inside individual subqueries unless wrapped in parentheses; sorting is applied to the final combined output at the very bottom.

-- Query A: Online Orders
SELECT order_id, customer_id, order_date, total_amount, 'online' AS channel
FROM online_orders
 
UNION ALL
 
-- Query B: Retail Store Orders
SELECT order_id, customer_id, order_date, total_amount, 'retail' AS channel
FROM retail_store_orders
ORDER BY order_date DESC;

2. Visual Comparison: Duplicate Elimination

Consider two simple tables containing regional warehouse employee IDs:

North Region (north_employees)

South Region (south_employees)

Notice that employee 102 (Marcus Vance) appears in both tables due to a dual-region assignment.

Result with UNION (Deduplicated):

SELECT emp_id, name, department FROM north_employees
UNION
SELECT emp_id, name, department FROM south_employees;

Returned 4 rows. Marcus Vance was scanned twice, hashed, and deduplicated.

Result with UNION ALL (Raw Append):

SELECT emp_id, name, department FROM north_employees
UNION ALL
SELECT emp_id, name, department FROM south_employees;

Returned 5 rows. No sorting or hashing was required; execution was instantaneous.

3. Why UNION ALL Is Faster: Execution Plan Mechanics

When you execute UNION ALL, the database query planner treats it as an Append operator:

1. Stream rows from Table 1 directly to the client.
2. Stream rows from Table 2 directly to the client.
3. Terminate.

When you execute UNION, the database must insert an intermediate Sort or HashAggregate node:

1. Scan Table 1 and place rows into a memory work area (work_mem).
2. Scan Table 2 and insert rows into the same work area.
3. Sort the combined dataset by all columns or build a hash table to compare row signatures.
4. Discard duplicate signatures.
5. If the combined row volume exceeds work_mem, spill temporary sort files to disk (causing substantial I/O latency).

4. Common Real-World Scenarios

Scenario 1: Combining Disjoint Historical Tables

If you partition orders by year (orders_2025, orders_2026), an order ID can never appear in both tables simultaneously. Using UNION here forces the database to sort millions of rows to find duplicates that you already know cannot exist. Always use UNION ALL for partitioned tables.

Scenario 2: Normalizing Multi-Column Flags to Rows

When reshaping survey responses or attribution channels:

SELECT user_id, 'email' AS source FROM newsletter_subscribers
UNION ALL
SELECT user_id, 'sms' AS source FROM sms_opt_ins;

Using UNION ALL accurately preserves users who signed up for both channels, allowing downstream counts to accurately reflect multi-touch attribution.

5. Frequently Asked Questions

What is the core difference between UNION and UNION ALL?

UNION combines the results of two queries and removes duplicate rows by executing an implicit sort and deduplication pass. UNION ALL combines the results directly and retains all rows, including duplicates, without sorting.

Why is UNION ALL faster than UNION?

UNION ALL simply concatenates the two result sets into a single stream. UNION must perform an in-memory hash aggregation or disk-based sort to identify and eliminate identical rows, adding significant CPU and memory overhead.

Do column names have to match in UNION?

No, column names do not need to match. The resulting column headers are determined by the first SELECT query. However, the number of columns, their positional order, and compatible data types must match across all queries.

When should I use UNION instead of UNION ALL?

Use UNION only when you strictly require unique records across both datasets and your queries do not already guarantee disjoint sets. If you know the two datasets are mutually exclusive, always use UNION ALL.

Conclusion

Understanding the difference between UNION and UNION ALL is essential for writing efficient, production-grade SQL. By defaulting to UNION ALL, you avoid unnecessary sort operations, reduce memory footprint, and prevent query timeouts across large datasets.