When working with SQL queries, especially in MySQL, combining results from multiple queries is a common task. Two powerful tools for this purpose are UNION
and UNION ALL
. While they might look similar, their behavior and use cases differ significantly. In this article, we’ll explain their differences clearly and show how to use them effectively.
🔰 What Are UNION and UNION ALL?
Both UNION
and UNION ALL
allow you to combine the results of two or more SELECT
statements. However, they differ in how they handle duplicates.
Operator | Removes Duplicates? | Preserves All Rows? | Performance Impact |
---|---|---|---|
UNION | ✅ Yes | ❌ No | Slower (due to sorting and deduplication) |
UNION ALL | ❌ No | ✅ Yes | Faster (no deduplication) |
🧪 Example Usage
Sample Tables
Let’s assume we have two tables:
-- products_a table
+----+----------+
| id | name |
+----+----------+
| 1 | Apple |
| 2 | Banana |
| 3 | Cherry |
+----+----------+
-- products_b table
+----+----------+
| id | name |
+----+----------+
| 1 | Banana |
| 2 | Durian |
| 3 | Elderberry |
+----+----------+
Example 1: Using UNION
SELECT name FROM products_a
UNION
SELECT name FROM products_b;
Result:
Apple
Banana
Cherry
Durian
Elderberry
✅ Duplicates like “Banana” are removed.
Example 2: Using UNION ALL
SELECT name FROM products_a
UNION ALL
SELECT name FROM products_b;
Result:
Apple
Banana
Cherry
Banana
Durian
Elderberry
✅ All rows are shown, including duplicates.
⚠️ Key Points to Remember
UNION
performs an implicitDISTINCT
operation to remove duplicates.UNION ALL
does not remove duplicates, making it more efficient.- Use
UNION
when data uniqueness is essential. - Use
UNION ALL
when performance matters and duplicates are acceptable.
🔍 Performance Comparison
For large datasets, UNION ALL
is significantly faster than UNION
because it skips the sorting and deduplication steps.
If you’re processing thousands (or millions) of rows and don’t need uniqueness, prefer UNION ALL
to avoid unnecessary overhead.
💡 Practical Tips
- You can combine more than two queries:
SELECT name FROM table1
UNION ALL
SELECT name FROM table2
UNION ALL
SELECT name FROM table3;
- Be mindful of column alignment. The number and data types of columns in all
SELECT
statements must match. - You can use
ORDER BY
only once, at the end of the finalSELECT
.
SELECT name FROM table1
UNION
SELECT name FROM table2
ORDER BY name;
📝 Summary
Feature | UNION | UNION ALL |
---|---|---|
Duplicates | Removed | Preserved |
Speed | Slower (due to sorting) | Faster |
Use Case | Need unique values | Need full results |
📚 Further Reading
If you found this article helpful, feel free to share it or explore more SQL tips on Thousand Tech Blog.
コメント