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.

How Does UNION Remove Duplicate Rows?

UNION stacks the rows of both SELECT statements and returns each distinct row only once. A row counts as a duplicate only when every selected column matches, and two NULLs in the same column count as equal, so customer 123 appears once only if its name is also identical in both tables.

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

Does UNION ALL Keep Duplicates?

Yes. UNION ALL returns every row from every SELECT with no duplicate check, so its row count is always the sum of the row counts of the individual queries. A customer in both tables appears twice, once per source.

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

Stacking current and archived copies of the same table is the classic UNION ALL job. Add a literal column such as 'Current' or 'Archive' to record where each row came from, and put one ORDER BY at the very end: it sorts the combined result, not just the last SELECT.

-- 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

Default to UNION ALL. Switch to UNION only when duplicate rows can actually occur, across the queries or within one of them, and the final result must be distinct.

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

UNION and UNION ALL follow the same rules, and both match columns by position, not by name. In SELECT a, b ... UNION ALL SELECT b, a ... the second query's b values land in column a, with no warning when the types are compatible.

• 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.