UNION vs UNION ALL in SQL

Posted by Kyle Hankinson November 15, 2025

---

UNION and UNION ALL both combine results from multiple SELECT statements, but they handle duplicate rows differently — and the performance difference can be significant.

The Difference

• UNION removes duplicate rows from the combined result (like an implicit DISTINCT)
• UNION ALL keeps all rows, including duplicates

-- Table A: 1, 2, 3
-- Table B: 2, 3, 4

SELECT id FROM table_a
UNION
SELECT id FROM table_b;
-- Result: 1, 2, 3, 4 (duplicates removed)

SELECT id FROM table_a
UNION ALL
SELECT id FROM table_b;
-- Result: 1, 2, 3, 2, 3, 4 (all rows kept)

Performance

UNION must sort or hash the entire result set to find and remove duplicates. On large datasets, this is expensive.

UNION ALL simply appends the results — no additional processing.

Rule of thumb: Use UNION ALL unless you specifically need duplicate removal. In practice, this is the majority of cases.

When UNION ALL is Correct

If the source queries cannot produce overlapping rows, deduplication is wasted work:

-- Orders from different months — no overlap possible
SELECT * FROM orders WHERE order_date >= '2025-01-01' AND order_date < '2025-02-01'
UNION ALL
SELECT * FROM orders WHERE order_date >= '2025-02-01' AND order_date < '2025-03-01';

-- Different tables entirely
SELECT 'customer' AS source, name, email FROM customers
UNION ALL
SELECT 'vendor' AS source, name, email FROM vendors;

When UNION is Correct

Use UNION when you genuinely need to deduplicate:

-- Find all cities where we have customers OR offices
SELECT city FROM customers
UNION
SELECT city FROM offices;

Rules for UNION / UNION ALL

Both require:

1. Same number of columns in each SELECT
2. Compatible data types (the database will attempt implicit conversion)

-- This works
SELECT name, email FROM customers
UNION ALL
SELECT company_name, contact_email FROM vendors;

-- This fails (different column counts)
SELECT name, email, phone FROM customers
UNION ALL
SELECT company_name, contact_email FROM vendors;

Column names in the result come from the first SELECT statement.

ORDER BY with UNION

ORDER BY applies to the final combined result. Place it after the last SELECT:

SELECT name, 'customer' AS type FROM customers
UNION ALL
SELECT name, 'vendor' AS type FROM vendors
ORDER BY name;

You cannot put ORDER BY inside individual SELECT statements (except in subqueries).

UNION ALL with Aggregation

A common pattern — combine data then aggregate:

WITH all_transactions AS (
    SELECT amount, transaction_date FROM sales
    UNION ALL
    SELECT -amount, transaction_date FROM refunds
)
SELECT
    DATE_TRUNC('month', transaction_date) AS month,
    SUM(amount) AS net_revenue
FROM all_transactions
GROUP BY 1
ORDER BY 1;

Combining More Than Two Queries

You can chain multiple UNIONs:

SELECT name FROM customers
UNION ALL
SELECT name FROM vendors
UNION ALL
SELECT name FROM employees
ORDER BY name;

INTERSECT and EXCEPT

Related set operations worth knowing:

-- Rows in both queries
SELECT city FROM customers
INTERSECT
SELECT city FROM offices;

-- Rows in the first query but not the second
SELECT city FROM customers
EXCEPT            -- SQL Server, PostgreSQL, SQLite
SELECT city FROM offices;

-- Oracle uses MINUS instead of EXCEPT
SELECT city FROM customers
MINUS
SELECT city FROM offices;

MySQL 8.0.31+ supports INTERSECT and EXCEPT. Earlier versions do not.

---