Need to combine results from two or more queries—but keep all the rows, even the duplicates? UNION ALL is the SQL tool for that. It’s simple, fast, and doesn’t filter your data. Let’s break it down.
How to Use UNION ALL
You can stack queries vertically with UNION ALL. It keeps every row from each result set.
Example
SELECT name, age FROM customers
UNION ALL
SELECT name, age FROM employees;
If “Alice, 30” appears in both, it’ll show twice.
When You’d Use It
- Combining logs, exports, or regional data
- Avoiding deduplication overhead
- Ensuring every transaction/event is counted
SELECT * FROM sales_america
UNION ALL
SELECT * FROM sales_asia;
Gotchas to Avoid
- Match the number of columns
- Match compatible data types
- Put
ORDER BYat the very end only - The result columns will be named after the first SELECT
SELECT email FROM users
UNION ALL
SELECT contact FROM customers
ORDER BY email;
Best Practices
- Always check column count and type compatibility.
- Use
UNION ALLfor speed unless duplicates are a problem. - Benchmark large UNION ALL queries—they're fast, but still read-heavy.
- Use aliases to align column labels.
FAQ
UNION vs UNION ALL?
UNION removes duplicates. UNION ALL keeps them. The latter is faster and accurate when duplicates are real data.
Can I use different column names?
Yes, but column names in the output come from the first query.
Should I wrap each SELECT in parentheses?
No need unless you’re mixing with subqueries or conditions.
Can I JOIN instead of UNION?
Only if you want to merge columns, not rows. JOIN is horizontal, UNION is vertical.
Conclusion
UNION ALL is a great tool when every row matters. It’s perfect for raw data, analytics pipelines, and multi-source reports. Use it wisely, and your queries will stay accurate and fast.
Read SQL UNION ALL: Keeping Duplicates When Combining Result Sets for more details.
Top comments (0)