UNION vs UNION ALL in SQL: Difference, Speed, and BigQuery Syntax

Both UNION and UNION ALL combine results from multiple queries. The key difference: UNION removes duplicates, UNION ALL keeps everything. This affects performance significantly.

UNION (Removes Duplicates)

SELECT customer_id, name FROM active_customers
UNION
SELECT customer_id, name FROM vip_customers;

-- If customer 123 is in both tables, appears only once

UNION ALL (Keeps Duplicates)

SELECT customer_id, name FROM active_customers
UNION ALL
SELECT customer_id, name FROM vip_customers;

-- If customer 123 is in both tables, appears twice

Is UNION ALL Faster Than UNION?

Yes — UNION ALL is almost always faster, and the gap widens with data volume. UNION must sort or hash the entire combined result to find and remove duplicates, which costs CPU and memory. UNION ALL simply appends one result set to the other with no comparison work. On large tables the difference can be dramatic.

-- SLOWER: UNION must sort and compare to find duplicates
SELECT order_id FROM orders_2023
UNION
SELECT order_id FROM orders_2024;

-- FASTER: UNION ALL just appends results
SELECT order_id FROM orders_2023
UNION ALL
SELECT order_id FROM orders_2024;

Does UNION Remove Duplicates From a Single Table?

Yes. UNION deduplicates the entire combined result set — including rows that were already duplicated inside just one of the SELECTs. If a value appears three times in the first query and never in the second, UNION still collapses it to one row. In that sense UNION behaves like applying SELECT DISTINCT to the whole result.

-- 'pending' appears 500 times in orders, but only once here:
SELECT status FROM orders
UNION
SELECT status FROM archived_orders;

-- Equivalent to:
SELECT DISTINCT status FROM (
    SELECT status FROM orders
    UNION ALL
    SELECT status FROM archived_orders
) AS combined;

BigQuery: UNION ALL vs UNION DISTINCT

BigQuery requires you to state the keyword explicitly — a bare UNION is a syntax error. Write UNION ALL to keep duplicates or UNION DISTINCT to remove them. UNION DISTINCT is exactly what other databases call plain UNION. The same rule applies to INTERSECT DISTINCT and EXCEPT DISTINCT.

-- BigQuery: keep duplicates
SELECT customer_id FROM `project.dataset.orders_2023`
UNION ALL
SELECT customer_id FROM `project.dataset.orders_2024`;

-- BigQuery: remove duplicates (= plain UNION elsewhere)
SELECT customer_id FROM `project.dataset.orders_2023`
UNION DISTINCT
SELECT customer_id FROM `project.dataset.orders_2024`;

-- Syntax error in BigQuery:
-- SELECT ... UNION SELECT ...   (keyword ALL or DISTINCT required)

Practical Example: Combining Tables

-- Get all transactions from current and archived tables
SELECT transaction_id, amount, transaction_date, 'Current' as source
FROM current_transactions
UNION ALL
SELECT transaction_id, amount, transaction_date, 'Archive' as source
FROM archived_transactions
ORDER BY transaction_date DESC;

When to Use Each

Use UNION when:	Use UNION ALL when:
You need distinct results only	Duplicates are impossible or wanted
Data quality requires deduplication	Performance is critical
Working with small datasets	Combining partitioned tables

Requirements

• Same number of columns in each query
• Compatible data types (same order)
• Column names from first query are used

UNION stacks rows vertically; to combine columns from different tables side by side you want a join instead — see UNION vs JOIN. And when you need rows that exist in one set but not the other, reach for EXCEPT and INTERSECT.