SQL UNION – Merge Query Results with UNION and UNION ALL
Introduction – What is SQL UNION?
The UNION operator in SQL lets you combine the results of two or more SELECT queries into a single result set. It removes duplicates by default, and it’s often used when queries return similar data from different tables or filters.
In this guide, you’ll learn:
- Syntax and rules for
UNIONandUNION ALL - Differences between the two
- Real-world use cases and tips
1. Basic SQL UNION Syntax
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;
Combines two result sets, removes duplicate rows.
2. UNION ALL – Keep All Rows (Duplicates Included)
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;
Returns all rows including duplicates.
3. UNION vs UNION ALL
| Operator | Duplicates | Performance | Use Case |
|---|---|---|---|
UNION | Removed | Slightly slower | Combine without duplicates |
UNION ALL | Preserved | Faster | Include all rows (even duplicates) |
4. UNION Requirements
- All
SELECTqueries must have the same number of columns - Corresponding columns must be of compatible data types
- Column names in the final result use the first query’s column names
5. Example – Multiple User Sources
SELECT name, email FROM customers
UNION
SELECT name, email FROM employees;
Creates a unified contact list.
6. ORDER BY with UNION
SELECT name FROM customers
UNION
SELECT name FROM suppliers
ORDER BY name;
ORDER BY applies to the entire result set, not individual queries.
Best Practices
| Recommended | Avoid This |
|---|---|
Use UNION when deduplication is needed | Expecting column mismatch to work |
Use UNION ALL for faster performance | Sorting inside individual queries |
| Match data types and positions exactly | Forgetting ORDER BY applies globally |
Summary – Recap & Next Steps
The UNION operator merges query outputs for unified reporting or combined filters. It helps simplify multiple queries into one result.
Key Takeaways:
- Use
UNIONto remove duplicates - Use
UNION ALLto keep them - Ensure matching columns and data types
- Final
ORDER BYsorts the full result set
Real-World Relevance:
Used in data exports, combined analytics, log aggregation, and multi-source reports.
Next: Explore INTERSECT, EXCEPT, and conditional CASE expressions.
FAQ – SQL UNION
What is the difference between UNION and UNION ALL?
UNION removes duplicates; UNION ALL does not.
Can I use ORDER BY in both queries?
No. Use ORDER BY once at the end.
Can UNION work with different column names?
Yes, but only the column names from the first query are used.
Does UNION preserve row order?
No. Use ORDER BY to control output order.
Share Now :
