MySQL UNION

The UNION operator combines the result sets of two or more SELECT statements into a single result. By default, UNION removes duplicate rows from the combined output. To keep all rows including duplicates, use UNION ALL.

Rules for UNION

Every SELECT in a UNION must follow two rules:

Each SELECT must return the same number of columns.
The corresponding columns must have compatible data types (e.g., both VARCHAR, or both INT).

The column names in the final result come from the first SELECT statement.

Syntax

SELECT column1, column2 FROM table_a
UNION
SELECT column1, column2 FROM table_b;

UNION Example — Removing Duplicates

Suppose you have two tables — one for current customers and one for archived customers — and you want a single list of all unique cities:

SELECT city FROM customers
UNION
SELECT city FROM archived_customers
ORDER BY city;

city
Chittagong
Dhaka
Rajshahi
Sylhet

Even if "Dhaka" appears in both tables, it only appears once in the result because UNION removes duplicates.

Note: The ORDER BY clause goes at the very end — after all the SELECT statements — and applies to the entire combined result.

UNION ALL — Keep All Rows

UNION ALL skips the duplicate-removal step, which makes it faster. Use it when you know there are no duplicates or when you intentionally want to count all occurrences:

SELECT city FROM customers
UNION ALL
SELECT city FROM archived_customers
ORDER BY city;

city
Chittagong
Dhaka
Dhaka
Rajshahi
Sylhet

"Dhaka" now appears twice — once from each table.

Performance tip: Always prefer UNION ALL when you know duplicates cannot exist or do not matter. UNION must sort and compare all rows to eliminate duplicates, which adds overhead on large result sets.

UNION with Multiple Columns

You can combine full rows, not just single columns. This example merges active orders with a historical orders table into one report:

SELECT id, customer_id, amount, 'active' AS status
FROM orders
UNION ALL
SELECT id, customer_id, amount, 'archived' AS status
FROM orders_archive
ORDER BY id;

A literal string 'active' or 'archived' is used as a tag column so you can tell which table each row came from.

Key Points to Remember

UNION removes duplicate rows; UNION ALL keeps them all.
All SELECT statements in a union must return the same number of columns with compatible types.
Column names in the result come from the first SELECT.
A single ORDER BY at the end sorts the entire combined result.
Use UNION ALL for better performance when duplicates are not a concern.

What's next? The next lesson covers Subqueries — writing a SELECT statement nested inside another query to filter or compute values dynamically.

« Previous Lesson Next Lesson »