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.