MySQL UNION ALL
In this page:
UNION vs UNION ALL
UNION and UNION ALL both combine the results of two or more SELECT statements into a single result set, but UNION automatically removes duplicate rows from that combined result, while UNION ALL keeps every row exactly as returned, duplicates included.
Example: UNION vs UNION ALL
CREATE TABLE t1 (name TEXT);
CREATE TABLE t2 (name TEXT);
INSERT INTO t1 VALUES ('Amit');
INSERT INTO t2 VALUES ('Amit');
SELECT name FROM t1 UNION SELECT name FROM t2;
SELECT name FROM t1 UNION ALL SELECT name FROM t2;Basic UNION ALL Syntax
UNION ALL uses the same syntax as UNION -- two or more SELECT statements separated by the UNION ALL keyword -- and requires each SELECT to return the same number of columns with compatible data types in the same order.
Example: Basic UNION ALL Syntax
CREATE TABLE online_sales (product TEXT, amount INT);
CREATE TABLE store_sales (product TEXT, amount INT);
INSERT INTO online_sales VALUES ('Pen', 10);
INSERT INTO store_sales VALUES ('Pencil', 5);
SELECT product, amount FROM online_sales
UNION ALL
SELECT product, amount FROM store_sales;Duplicate Rows are Kept
Because UNION ALL skips the deduplication step entirely, if the same row happens to appear in the results of more than one SELECT statement, that row will appear multiple times in the final combined output.
Example: Duplicate Rows are Kept
CREATE TABLE t1 (name TEXT);
CREATE TABLE t2 (name TEXT);
INSERT INTO t1 VALUES ('Amit');
INSERT INTO t2 VALUES ('Amit');
SELECT name FROM t1 UNION ALL SELECT name FROM t2;Performance Differences
UNION ALL is generally faster than UNION because removing duplicates requires MySQL to sort and compare every row in the combined result, work that UNION ALL skips entirely by simply appending the result sets together.
Example: Performance Differences
-- UNION: must sort and compare every row to remove duplicates
SELECT name FROM t1 UNION SELECT name FROM t2;
-- UNION ALL: skips deduplication entirely, so it's faster
SELECT name FROM t1 UNION ALL SELECT name FROM t2;Choosing Between UNION and UNION ALL
UNION ALL is the better choice when duplicate rows are either impossible, expected, or meaningful to keep, or when performance matters more than deduplication, while UNION is worth its extra cost when a clean, duplicate-free result is actually required.
Example: Choosing Between UNION and UNION ALL
CREATE TABLE web_logs (event TEXT);
CREATE TABLE app_logs (event TEXT);
INSERT INTO web_logs VALUES ('click');
INSERT INTO app_logs VALUES ('click');
-- Duplicates are meaningful here -- count every event
SELECT event FROM web_logs UNION ALL SELECT event FROM app_logs;Chapter Quiz — Complete all 10 topics to unlock
0/10 topics done
Complete these topics first: