MySQL UNION ALL
In this page:
SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2;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;- Using
UNION ALLwhen duplicates are not wanted and getting repeated rows. - Assuming column names come from the second
SELECT, when the result uses the first query's names. - Combining columns in a different order, so values of different meanings end up in the same column.
- Joins combine rows from multiple tables, including
INNER JOIN,LEFT JOIN, andRIGHT JOIN. - Self joins, cross joins, and multiple table joins handle more advanced relationships.
UNIONandUNION ALLcombine the results of several queries into one.
Chapter Quiz — Complete all 10 topics to unlock
0/10 topics done
Complete these topics first: