MySQL Union

Combining Result Sets with UNION

UNION stacks the rows returned by two or more SELECT statements on top of each other to produce a single combined result. Unlike a JOIN, which combines columns side by side, UNION combines rows — so each SELECT in the union must return the same number of columns, and the columns in matching positions must have compatible data types.

UNION vs UNION ALL

Plain UNION automatically removes duplicate rows from the combined result, which means MySQL has to sort or hash the entire result set to find and discard repeats. UNION ALL skips that deduplication step entirely and simply concatenates the result sets, which makes it noticeably faster on large data. Use UNION ALL whenever you know the sources won't overlap or you don't care about duplicates.

• Every SELECT in the UNION must return the same number of columns
• Columns in the same position must have compatible data types
• The output column names are taken from the first SELECT statement
• Only one ORDER BY is allowed, and it must appear after the final SELECT, applying to the whole combined result

SELECT name, email FROM active_customers
UNION
SELECT name, email FROM archived_customers;

Because ORDER BY sorts the final combined output rather than each individual SELECT, you write it only once, at the very end of the statement, and it can reference the column names or aliases established by the first SELECT.

SELECT name, email FROM active_customers
UNION ALL
SELECT name, email FROM archived_customers
ORDER BY name;

SELECT name, 'employee' AS person_type FROM employees
UNION ALL
SELECT name, 'contractor' AS person_type FROM contractors
ORDER BY person_type, name;