💻 Introduction
UNION and UNION ALL are SQL set operators that combine the result of two or more SELECT queries.
- UNION: Combines results and removes duplicates.
- UNION ALL: Combines results but keeps duplicates.
—
1️⃣ SQL UNION Example
Removes duplicate records across queries.
SELECT city FROM Customers
UNION
SELECT city FROM Suppliers;
Output: List of unique cities from both tables.
—
2️⃣ SQL UNION ALL Example
Keeps all duplicates across queries (faster than UNION).
SELECT city FROM Customers
UNION ALL
SELECT city FROM Suppliers;
Output: List of all cities including duplicates.
—
3️⃣ Key Differences Between UNION and UNION ALL
| Feature | UNION | UNION ALL |
|---|---|---|
| Duplicates | Removed | Included |
| Performance | Slower (extra step to remove duplicates) | Faster |
| Use Case | Need unique values | Need all values, including duplicates |
—
4️⃣ Important Rules
- Each
SELECTinside UNION must have the same number of columns. - The columns must have compatible data types.
- Column order must match.
⚠️ Tip: If you don’t need duplicates removed, always prefer UNION ALL for better performance.
—
✅ Quick Recap
- UNION = Distinct results
- UNION ALL = All results (duplicates allowed)
- Performance: UNION ALL > UNION
📚 Continue Learning
- Previous: SQL Joins
- Next: INTERSECT & EXCEPT
💬 Do you use UNION or UNION ALL more often? Share your scenario in the comments!