SQL UNION ALL: The Fastest Way to Combine Result Sets


When merging results in SQL, the UNION
operator often gets the spotlight. But what if you want every single row—including duplicates? Enter UNION ALL
. It’s faster, simpler, and ideal when duplication is not an issue but a feature. Here's how it works.
How UNION ALL Works
UNION ALL
takes multiple SELECT
queries and stacks their results. Unlike UNION
, it does not filter out duplicates.
Example:
SELECT name FROM customers
UNION ALL
SELECT name FROM employees;
This query combines all rows, including repeated names like “Alice”.
Why Use UNION ALL?
Performance: No deduplication step
Accuracy: Preserves repeated events (like duplicate sales)
Simplicity: Easier syntax without overhead
Use Case: Geographic Partitions
SELECT * FROM orders_us
UNION ALL
SELECT * FROM orders_eu;
This allows analytics tools to work with unified data without risk of unintentional data loss from deduplication.
Query Notes
Column alignment matters: same number and types
The first SELECT defines column names
Only one ORDER BY clause at the end is valid
Best Practices
Only use
UNION
when deduplication is essential.Keep your queries readable—alias differing column names.
Benchmark queries if performance matters.
Use with care when order matters; rows may appear unsorted.
FAQ
UNION vs UNION ALL — Which should I use?
Use UNION ALL
when duplicates are okay or expected. It’s faster and doesn’t filter.
Can I mix data types in columns?
Columns must be compatible (e.g., VARCHAR
with TEXT
, INT
with FLOAT
).
Should I alias columns when names differ?
Yes, especially if you're merging fields like email
vs username
.
Is ORDER BY
allowed in each SELECT?
No. It belongs at the end unless you're using MySQL, which permits exceptions.
Conclusion
UNION ALL
is your go-to for combining datasets without sacrificing duplicates. It’s efficient, predictable, and essential when performance and completeness matter.
Read SQL UNION ALL: Keeping Duplicates When Combining Result Sets for more details.
Subscribe to my newsletter
Read articles from DbVisualizer directly inside your inbox. Subscribe to the newsletter, and don't miss out.
Written by

DbVisualizer
DbVisualizer
DbVisualizer is the database client with the highest user satisfaction. It is used for development, analytics, maintenance, and more, by database professionals all over the world. It connects to all popular databases and runs on Win, macOS & Linux.