MySQL UNION Operator
In this page:
What Is UNION?
UNION combines the result sets of two or more SELECT statements into a single result set, stacking their rows on top of each other rather than matching columns side by side like a JOIN does. Every SELECT in the union must return the same number of columns, and the columns must have compatible data types in the same order. It is commonly used to merge similar data pulled from separate tables, such as combining current customers with archived customers.
Example: What Is UNION?
CREATE TABLE current_customers (name TEXT);
CREATE TABLE archived_customers (name TEXT);
INSERT INTO current_customers VALUES ('Amit'), ('Priya');
INSERT INTO archived_customers VALUES ('Rahul');
SELECT name FROM current_customers
UNION
SELECT name FROM archived_customers;UNION Removes Duplicates
By default, UNION compares every row across the combined result sets and removes exact duplicate rows, the same way SELECT DISTINCT would on a single query. This deduplication has a real performance cost on large result sets, since MySQL has to sort and compare rows to find matches. If you know your source data is already unique, or you specifically want to keep duplicates, UNION ALL is the faster choice.
Example: UNION Removes Duplicates
CREATE TABLE current_customers (name TEXT);
CREATE TABLE archived_customers (name TEXT);
INSERT INTO current_customers VALUES ('Amit');
INSERT INTO archived_customers VALUES ('Amit');
SELECT name FROM current_customers
UNION
SELECT name FROM archived_customers;UNION ALL Keeps Duplicates
UNION ALL simply appends every row from each SELECT statement without checking for duplicates, which makes it noticeably faster than plain UNION on large tables since MySQL skips the deduplication pass entirely. Use it whenever duplicate rows across the combined sets are acceptable, or when you actually want a count that includes repeats, such as totaling activity from two log tables.
Example: UNION ALL Keeps Duplicates
CREATE TABLE current_customers (name TEXT);
CREATE TABLE archived_customers (name TEXT);
INSERT INTO current_customers VALUES ('Amit');
INSERT INTO archived_customers VALUES ('Amit');
SELECT name FROM current_customers
UNION ALL
SELECT name FROM archived_customers;Column Count and Type Rules
Each SELECT statement in a UNION must project the exact same number of columns, and MySQL matches them by position, not by name -- the final result set uses the column names from the first SELECT. If corresponding columns have different but compatible types, such as one being INT and another DECIMAL, MySQL implicitly converts them to a common type. Mismatched column counts raise an error immediately.
Example: Column Count and Type Rules
CREATE TABLE online_sales (product TEXT, amount INT);
CREATE TABLE store_sales (product TEXT, amount DECIMAL(10,2));
INSERT INTO online_sales VALUES ('Pen', 10);
INSERT INTO store_sales VALUES ('Pencil', 5.50);
SELECT product, amount FROM online_sales
UNION
SELECT product, amount FROM store_sales;Ordering a UNION Result
You can only attach one ORDER BY clause to a UNION query, and it must come after the final SELECT, applying to the combined result as a whole rather than to each individual SELECT. Ordering an individual SELECT before the UNION is not meaningful, since the union's own row order is not guaranteed until the final ORDER BY is applied. LIMIT works the same way, applying only to the combined output.
Example: Ordering a UNION Result
CREATE TABLE current_customers (name TEXT);
CREATE TABLE archived_customers (name TEXT);
INSERT INTO current_customers VALUES ('Priya');
INSERT INTO archived_customers VALUES ('Amit');
SELECT name FROM current_customers
UNION
SELECT name FROM archived_customers
ORDER BY name;Chapter Quiz — Complete all 10 topics to unlock
0/10 topics done
Complete these topics first: