UNION — MySQL | LetCodes

UNION and Set Operations in MySQL

UNION combines the result sets of two or more SELECT queries into a single result set. Unlike JOINs (which combine columns side-by-side), UNION stacks result sets on top of each other. It is the SQL equivalent of appending one list to another.

UNION vs UNION ALL — Performance Impact

This is the single most important distinction when using UNION:

UNION removes duplicate rows from the combined result. It must sort or hash the entire combined result set to eliminate duplicates — this is an extra pass over potentially millions of rows. Always slower than UNION ALL.
UNION ALL keeps every row including duplicates — no deduplication, no extra work. Always faster.

SQL

-- UNION: deduplicates — a customer in both tables appears only once
-- MySQL performs an implicit DISTINCT over the full combined result
SELECT customer_id, email FROM customers_us
UNION
SELECT customer_id, email FROM customers_eu;

-- UNION ALL: keeps all rows including duplicates — no sort step, always faster
SELECT customer_id, email FROM customers_us
UNION ALL
SELECT customer_id, email FROM customers_eu;

-- EXPLAIN shows the difference:
EXPLAIN
SELECT customer_id FROM customers_us UNION SELECT customer_id FROM customers_eu;
-- Extra: "Using temporary; Using filesort"  <-- extra dedup work

EXPLAIN
SELECT customer_id FROM customers_us UNION ALL SELECT customer_id FROM customers_eu;
-- Extra: (nothing or "Using index")  <-- no dedup

Note

Always use UNION ALL unless you specifically need duplicate removal. The deduplication in UNION requires an implicit DISTINCT pass over the entire combined result, which can be slow on large data sets.

Column Count and Type Compatibility Rules

For a UNION to be valid, every SELECT in the union must have:

The same number of columns.
Columns in compatible data types in the same positional order.

If types differ, MySQL uses the broadest compatible type. The column names in the final result always come from the first SELECT statement. What happens on mismatch:

SQL

-- ERROR: column count mismatch
SELECT id, name FROM table_a
UNION
SELECT id FROM table_b;
-- ERROR 1222: The used SELECT statements have a different number of columns

-- Fix: use NULL as a placeholder for missing columns
SELECT id, name, NULL AS phone FROM table_a
UNION ALL
SELECT id, NULL,  phone        FROM table_b;

-- Type coercion: MySQL uses the "most general" type
-- INT + VARCHAR => VARCHAR in the result
SELECT 1 AS val
UNION ALL
SELECT 'hello' AS val;  -- result type is VARCHAR

-- Column names come from the FIRST SELECT
SELECT 'Source A' AS source, customer_id, email FROM customers_us
UNION ALL
SELECT 'Source B',            customer_id, email FROM customers_eu;
-- Column names: source, customer_id, email  (from first SELECT, always)

ORDER BY Placement Rules

You can only place one ORDER BY at the very end of a UNION query, and it sorts the entire combined result. You cannot add ORDER BY to individual SELECT statements within a UNION without parentheses.

SQL

-- ORDER BY applies to the combined result (must be at the very end)
SELECT customer_id, email, 'US' AS region FROM customers_us
UNION ALL
SELECT customer_id, email, 'EU'           FROM customers_eu
ORDER BY email;

-- Reference by column position when alias might be ambiguous
SELECT customer_id, email FROM customers_us
UNION ALL
SELECT customer_id, email FROM customers_eu
ORDER BY 2;     -- sorts by second column (email)

-- Wrap individual SELECTs in parentheses for inner ordering (MySQL 8.0+)
(SELECT customer_id, email, 1 AS sort_order FROM customers_us ORDER BY email LIMIT 5)
UNION ALL
(SELECT customer_id, email, 2 AS sort_order FROM customers_eu ORDER BY email LIMIT 5)
ORDER BY sort_order, email;

Warning

In MySQL 5.7, adding ORDER BY inside a UNION member SELECT without parentheses is silently ignored. Always wrap individual members in parentheses if you need inner ordering: (SELECT ... ORDER BY ... LIMIT n).

LIMIT with UNION — Must Wrap

A LIMIT at the end of a UNION applies to the whole combined result. To limit individual parts, wrap each in parentheses. To paginate the combined result, wrap the whole UNION in a subquery.

SQL

-- LIMIT on the full union (applies to combined result after dedup/sort)
SELECT customer_id, email FROM customers_us
UNION ALL
SELECT customer_id, email FROM customers_eu
ORDER BY email
LIMIT 20;

-- LIMIT on individual parts — must use parentheses
(SELECT customer_id, email FROM customers_us ORDER BY email LIMIT 10)
UNION ALL
(SELECT customer_id, email FROM customers_eu ORDER BY email LIMIT 10);
-- Returns up to 20 rows (10 from each)

-- Pagination of the full combined result — wrap in subquery
SELECT * FROM (
  SELECT customer_id, email, 'US' AS region, created_at FROM customers_us
  UNION ALL
  SELECT customer_id, email, 'EU',           created_at FROM customers_eu
) AS combined
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;   -- Page 3 (0-indexed, 10 per page)

Combining Audit and History Tables

A classic UNION ALL use case: data is split across a current table and one or more archive/history tables. Query them together as if they were one table.

SQL

-- Query current + archived orders as a unified view
CREATE VIEW all_orders AS
SELECT order_id, customer_id, total, status, created_at, 'current' AS source
FROM orders
UNION ALL
SELECT order_id, customer_id, total, status, created_at, 'archive'
FROM orders_archive;

-- Now query the view like a regular table
SELECT customer_id, SUM(total) AS lifetime_value
FROM all_orders
WHERE status = 'delivered'
GROUP BY customer_id
HAVING lifetime_value > 500;

-- The optimizer may push predicates into each branch
EXPLAIN SELECT * FROM all_orders WHERE customer_id = 42;

UNION with CTEs for Readable Complex Queries

SQL

-- Combine current + archived orders with CTE for readability
WITH current_orders AS (
  SELECT order_id, customer_id, total, status, created_at
  FROM orders
  WHERE created_at >= '2024-01-01'
),
archive_orders AS (
  SELECT order_id, customer_id, total, status, created_at
  FROM orders_archive
  WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01'
),
all_orders AS (
  SELECT * FROM current_orders
  UNION ALL
  SELECT * FROM archive_orders
)
SELECT
  DATE_FORMAT(created_at, '%Y-%m') AS month,
  COUNT(*)                          AS order_count,
  ROUND(SUM(total), 2)              AS revenue
FROM all_orders
WHERE status = 'delivered'
GROUP BY month
ORDER BY month;

INTERSECT and EXCEPT (MySQL 8.0.31+)

MySQL 8.0.31 added support for INTERSECT and EXCEPT.

INTERSECT returns rows that appear in both result sets (like INNER JOIN on all columns).
EXCEPT (or MINUS) returns rows from the first set that do not appear in the second set.

SQL

-- INTERSECT: customers who exist in BOTH the US and EU databases (MySQL 8.0.31+)
SELECT email FROM customers_us
INTERSECT
SELECT email FROM customers_eu;

-- EXCEPT: products sold in the US but NOT in the EU
SELECT product_id FROM us_order_items
EXCEPT
SELECT product_id FROM eu_order_items;

-- Emulating INTERSECT in older MySQL (< 8.0.31) with INNER JOIN
SELECT DISTINCT a.email
FROM customers_us AS a
INNER JOIN customers_eu AS b ON a.email = b.email;

-- Emulating INTERSECT with EXISTS
SELECT DISTINCT email FROM customers_us AS a
WHERE EXISTS (
  SELECT 1 FROM customers_eu AS b WHERE b.email = a.email
);

-- Emulating EXCEPT in older MySQL with LEFT JOIN + IS NULL
SELECT DISTINCT a.product_id
FROM us_order_items AS a
LEFT JOIN eu_order_items AS b ON a.product_id = b.product_id
WHERE b.product_id IS NULL;

-- Emulating EXCEPT with NOT EXISTS
SELECT DISTINCT product_id FROM us_order_items
WHERE product_id NOT IN (SELECT product_id FROM eu_order_items);

Tip

For large tables, the LEFT JOIN + IS NULL pattern is often faster than NOT IN, because NOT IN with a subquery re-executes the subquery for every row. Use NOT EXISTS or LEFT JOIN for better performance on MySQL older than 8.0.31.

Practical Real-World Example: Unified Activity Feed

SQL

-- Activity feed combining orders, reviews, and support tickets
-- Each source has different columns — use NULL placeholders for alignment
SELECT
  'order'          AS event_type,
  o.order_id       AS ref_id,
  o.customer_id,
  CONCAT('Order #', o.order_id, ' - $', o.total) AS description,
  o.created_at
FROM orders o

UNION ALL

SELECT
  'review',
  r.review_id,
  r.customer_id,
  CONCAT(r.rating, '-star review for product ', r.product_id),
  r.created_at
FROM reviews r

UNION ALL

SELECT
  'support_ticket',
  t.ticket_id,
  t.customer_id,
  CONCAT('Support: ', LEFT(t.subject, 50)),
  t.opened_at
FROM support_tickets t

ORDER BY created_at DESC
LIMIT 50;

Global WHERE Filter Across UNION

SQL

-- WRONG: WHERE after last SELECT only filters orders_2024
SELECT order_id, customer_id, total FROM orders_2022
UNION ALL
SELECT order_id, customer_id, total FROM orders_2023
UNION ALL
SELECT order_id, customer_id, total FROM orders_2024
WHERE total > 100;   -- This applies only to orders_2024!

-- CORRECT: wrap union in a subquery for global filter
SELECT * FROM (
  SELECT order_id, customer_id, total FROM orders_2022
  UNION ALL
  SELECT order_id, customer_id, total FROM orders_2023
  UNION ALL
  SELECT order_id, customer_id, total FROM orders_2024
) AS all_orders
WHERE total > 100
ORDER BY total DESC;

Warning

When a WHERE clause follows the last SELECT in a UNION, MySQL applies it only to that last SELECT, not to the entire union. Always wrap the union in a subquery if you need a global filter.

UNION vs JOIN — Choosing the Right Tool

Operation	What it does	Use when
UNION ALL	Stacks rows vertically (more rows, same columns)	Combining similar data from separate tables
UNION	Same as UNION ALL but removes duplicates	Need deduplication, willing to pay the sort cost
JOIN	Combines tables horizontally (more columns)	Fetching related data from different entities
INTERSECT	Rows that appear in both results	Finding common members across two sets (8.0.31+)
EXCEPT	Rows in first result but not second	Finding members unique to one set (8.0.31+)

Default to UNION ALL — only use UNION when you actually need deduplication
Column count and positional types must match across all SELECT statements in the UNION
Column names come from the first SELECT statement
Put ORDER BY and LIMIT after the final SELECT to sort/limit the whole result
Wrap in a subquery when you need a global WHERE filter across the entire union
Use parentheses around individual SELECT members when you need inner ORDER BY or LIMIT
INTERSECT and EXCEPT require MySQL 8.0.31+; use JOIN patterns on older versions

Performance Tips for UNION Queries

SQL

-- Use UNION ALL when you know there are no duplicates (always faster)
-- Only use UNION when deduplication is actually required

-- EXPLAIN to check the execution plan of a UNION
EXPLAIN
SELECT customer_id FROM customers_us
UNION ALL
SELECT customer_id FROM customers_eu;
-- Look for: each branch should use indexes, no "Using temporary" on the whole union

-- UNION with indexes: filter as tightly as possible in each branch
-- The optimizer can push WHERE into each branch of a UNION
SELECT order_id, total FROM orders
WHERE created_at >= '2024-01-01' AND customer_id = 42
UNION ALL
SELECT order_id, total FROM orders_archive
WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01' AND customer_id = 42;
-- Each SELECT should use an index on (customer_id, created_at)

-- Adding a sort limit to each branch reduces rows early (before final sort)
(SELECT order_id, total, created_at FROM orders ORDER BY created_at DESC LIMIT 100)
UNION ALL
(SELECT order_id, total, created_at FROM orders_archive ORDER BY created_at DESC LIMIT 100)
ORDER BY created_at DESC
LIMIT 100;

UNION Inside a VIEW

SQL

-- Views can encapsulate a UNION for reuse
CREATE VIEW all_customers AS
SELECT
  'active'   AS customer_type,
  customer_id,
  email,
  country,
  created_at
FROM customers
WHERE is_active = 1
UNION ALL
SELECT
  'archived',
  customer_id,
  email,
  country,
  created_at
FROM customers_archive;

-- Query the view like a regular table
SELECT customer_type, country, COUNT(*) AS total
FROM all_customers
GROUP BY customer_type, country
ORDER BY country;

UNION Quick Reference

Rule	Details
Column count	All SELECT statements must have the same number of columns
Column types	Types must be compatible — MySQL uses the broadest type
Column names	Come from the FIRST SELECT statement in the UNION
ORDER BY	Only one, at the very end — sorts the entire combined result
LIMIT	Only one at the end, or one per part with parentheses
WHERE (global)	Wrap full UNION in a subquery to apply a global WHERE filter
UNION dedup cost	UNION runs an implicit DISTINCT — uses temp table + sort
UNION ALL speed	No dedup — always faster, use by default
INTERSECT support	MySQL 8.0.31+ natively; emulate with INNER JOIN on older versions
EXCEPT support	MySQL 8.0.31+ natively; emulate with LEFT JOIN + IS NULL on older versions

Common UNION Mistakes

Mistake	Symptom	Fix
Using UNION instead of UNION ALL	Slower than expected on large tables	Use UNION ALL unless deduplication is required
WHERE after last SELECT in UNION	Only the last branch is filtered	Wrap entire UNION in a subquery and apply WHERE to outer query
ORDER BY inside a UNION member (no parens)	Silently ignored in MySQL 5.7	Wrap the member SELECT in parentheses: (SELECT ... ORDER BY ... LIMIT n)
Different column counts	ERROR 1222: different number of columns	Add NULL placeholders to match column count
Column name from wrong SELECT	Aliases from later SELECTs are ignored	Set aliases in the FIRST SELECT of the UNION
Applying LIMIT per branch without parens	LIMIT applies to the whole UNION	Wrap each branch in parentheses: (SELECT ... LIMIT n) UNION ALL (SELECT ... LIMIT n)

UNION with Aggregation

After combining rows with UNION ALL, you can aggregate the full combined result by wrapping in a subquery:

SQL

-- Total revenue across current + archived orders
SELECT SUM(total) AS combined_revenue
FROM (
  SELECT total FROM orders
  UNION ALL
  SELECT total FROM orders_archive
) AS all_orders;

-- Count orders per status across all years
SELECT status, COUNT(*) AS total_orders
FROM (
  SELECT status FROM orders_2022
  UNION ALL
  SELECT status FROM orders_2023
  UNION ALL
  SELECT status FROM orders_2024
) AS all_orders
GROUP BY status
ORDER BY total_orders DESC;