Introduction
UNION and UNION ALL both combine result sets from multiple SELECT statements. The key difference is that UNION removes duplicate rows from the combined result (performing an implicit DISTINCT), while UNION ALL keeps all rows including duplicates. Because UNION must sort and deduplicate, it is slower than UNION ALL. Use UNION ALL when you know there are no duplicates or when duplicates are acceptable.
Basic Syntax
1-- UNION: removes duplicates
2SELECT column1, column2 FROM table_a
3UNION
4SELECT column1, column2 FROM table_b;
5
6-- UNION ALL: keeps duplicates
7SELECT column1, column2 FROM table_a
8UNION ALL
9SELECT column1, column2 FROM table_b;
Both require the same number of columns in each SELECT, and the columns must have compatible data types.
UNION (Removes Duplicates)
1-- Table: us_customers
2-- | name | city |
3-- | Alice | New York |
4-- | Bob | Chicago |
5
6-- Table: eu_customers
7-- | name | city |
8-- | Alice | New York |
9-- | Charlie | London |
10
11SELECT name, city FROM us_customers
12UNION
13SELECT name, city FROM eu_customers;
14
15-- Result (3 rows — duplicate "Alice, New York" removed):
16-- | name | city |
17-- | Alice | New York |
18-- | Bob | Chicago |
19-- | Charlie | London |
UNION compares entire rows. Two rows are considered duplicates only if all column values match.
UNION ALL (Keeps Duplicates)
1SELECT name, city FROM us_customers
2UNION ALL
3SELECT name, city FROM eu_customers;
4
5-- Result (4 rows — all rows kept):
6-- | name | city |
7-- | Alice | New York |
8-- | Bob | Chicago |
9-- | Alice | New York |
10-- | Charlie | London |
UNION must perform a sort or hash operation to identify and remove duplicates:
1-- UNION: SELECT + SELECT + SORT + DISTINCT
2-- Time complexity: O(n log n) for the deduplication step
3
4-- UNION ALL: SELECT + SELECT (no extra processing)
5-- Time complexity: O(n) — just concatenates results
For large result sets, the difference is significant. If table_a returns 1 million rows and table_b returns 1 million rows, UNION must sort all 2 million rows to find duplicates. UNION ALL simply returns all 2 million rows immediately.
1-- Check execution plan to see the sort operation
2EXPLAIN
3SELECT id, name FROM employees
4UNION
5SELECT id, name FROM contractors;
6-- Shows a "Sort" or "HashAggregate" node for deduplication
7
8EXPLAIN
9SELECT id, name FROM employees
10UNION ALL
11SELECT id, name FROM contractors;
12-- No sort node — faster execution
When to Use Each
Use UNION ALL when:
1-- 1. Tables have no overlapping data (e.g., partitioned by date)
2SELECT * FROM orders_2024
3UNION ALL
4SELECT * FROM orders_2025;
5
6-- 2. You want to count total rows including duplicates
7SELECT product_id FROM store_a_sales
8UNION ALL
9SELECT product_id FROM store_b_sales;
10-- Use this for "how many total sales across both stores"
11
12-- 3. Each SELECT already has DISTINCT or WHERE ensuring uniqueness
13SELECT DISTINCT customer_id FROM orders WHERE year = 2024
14UNION ALL
15SELECT DISTINCT customer_id FROM orders WHERE year = 2025;
Use UNION when:
1-- 1. You need a unique list from overlapping sources
2SELECT email FROM newsletter_subscribers
3UNION
4SELECT email FROM registered_users;
5-- Produces a deduplicated email list
6
7-- 2. Combining lookup values from multiple tables
8SELECT status_code, description FROM order_statuses
9UNION
10SELECT status_code, description FROM shipment_statuses;
Combining More Than Two Queries
1-- Chain multiple UNIONs
2SELECT name FROM employees
3UNION ALL
4SELECT name FROM contractors
5UNION ALL
6SELECT name FROM interns;
7
8-- Mix UNION and UNION ALL (use parentheses for clarity)
9(SELECT name FROM employees
10 UNION
11 SELECT name FROM contractors)
12UNION ALL
13SELECT name FROM interns;
ORDER BY and LIMIT with UNION
ORDER BY and LIMIT apply to the entire combined result, not to individual SELECT statements:
1-- Sort the combined result
2SELECT name, salary FROM employees
3UNION ALL
4SELECT name, salary FROM contractors
5ORDER BY salary DESC
6LIMIT 10;
7
8-- To sort individual SELECTs, use subqueries
9SELECT * FROM (
10 SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 5
11) AS top_employees
12UNION ALL
13SELECT * FROM (
14 SELECT name, salary FROM contractors ORDER BY salary DESC LIMIT 5
15) AS top_contractors;
Column Name and Type Rules
1-- Column names come from the FIRST SELECT
2SELECT first_name AS name, hire_date AS date FROM employees
3UNION ALL
4SELECT company_name, contract_date FROM contractors;
5-- Result columns are named "name" and "date"
6
7-- Types must be compatible (implicit casting occurs)
8SELECT id, price FROM products -- price is DECIMAL
9UNION ALL
10SELECT id, estimated_cost FROM quotes; -- estimated_cost is FLOAT
11-- Result column is cast to the wider type
UNION vs JOIN
1-- UNION: stacks rows vertically (more rows, same columns)
2-- JOIN: combines columns horizontally (same rows, more columns)
3
4-- UNION: "Give me all customers from both tables"
5SELECT name FROM us_customers
6UNION ALL
7SELECT name FROM eu_customers;
8
9-- JOIN: "Give me customer info paired with their orders"
10SELECT c.name, o.total
11FROM customers c
12JOIN orders o ON c.id = o.customer_id;
Common Pitfalls
Using UNION when UNION ALL suffices: If the source tables are already disjoint (e.g., partitioned by region or date), UNION wastes time sorting and deduplicating rows that have no duplicates. Default to UNION ALL and only use UNION when deduplication is explicitly needed.
Column count mismatch: Every SELECT in a UNION must return the same number of columns. If one query returns 3 columns and another returns 4, the query fails. Add NULL as a placeholder column if needed.
ORDER BY on individual SELECTs: Placing ORDER BY inside one of the SELECT statements (without a subquery) either causes an error or is ignored by the optimizer. Use ORDER BY after the last UNION to sort the final result.
NULL handling in UNION: UNION treats two NULL values as equal when deduplicating, so rows that differ only in having NULL in the same column are considered duplicates. This may remove rows you intended to keep.
Implicit type casting: If column types differ between SELECT statements, the database performs implicit casting which can cause data loss (e.g., truncating a VARCHAR(100) to VARCHAR(50)) or unexpected results. Explicitly cast columns to matching types.
Summary
UNION removes duplicates from the combined result (like SELECT DISTINCT on the final output)
UNION ALL keeps all rows including duplicates and is faster
Use UNION ALL by default; switch to UNION only when deduplication is required
Both require matching column counts and compatible types across all SELECT statements
ORDER BY and LIMIT apply to the entire combined result, not to individual queries
