UNION vs UNION ALL in SQL

UNION combines two query results and removes duplicate rows; UNION ALL combines them and keeps every row, which is significantly faster.

Published September 19, 2026

UNION combines two result sets and removes duplicate rows; UNION ALL combines them and keeps every row. Prefer UNION ALL unless you really need de-duplication, because it is much faster.

The concept

Both stack the results of two SELECTs vertically; the queries must return the same number of columns with compatible types. Column names come from the first query.

UNION must sort or hash to remove duplicates, which costs time and memory. UNION ALL just appends.

Same inputs

SELECT n FROM a UNION SELECT n FROM b;
1, 2, 3
SELECT n FROM a UNION ALL SELECT n FROM b;
1, 2, 2, 2, 3
A = {1,2,2}, B = {2,3}

Practical examples

Merge two tables

SELECT email FROM customers
UNION
SELECT email FROM newsletter_subscribers;

Tag the source

SELECT id, 'web' AS source FROM web_orders
UNION ALL
SELECT id, 'app' FROM app_orders;

Order the combined result

(SELECT name FROM a) UNION ALL (SELECT name FROM b) ORDER BY name;

Common mistakes

  • Using UNION when rows are known to be distinct and paying for needless de-duplication.
  • Different column counts or order between the SELECTs.
  • Putting ORDER BY inside the first SELECT only.

Key takeaways

  • UNION = distinct, UNION ALL = keep all.
  • Same column count and compatible types.
  • Default to UNION ALL.

FAQ

Can I union more than two queries?

Yes, chain as many as you need.

What about INTERSECT and EXCEPT?

They return common rows and rows only in the first set (MySQL 8.0.31+, PostgreSQL).

More SQL articles