Complete Guide to UNION vs UNION ALL in MySQL – Explained for Beginners

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.

🧪 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 implicit DISTINCT 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 final SELECT.

SELECT name FROM table1
UNION
SELECT name FROM table2
ORDER BY name;

📝 Summary

📚 Further Reading

• MySQL UNION Documentation
• Performance Tuning in MySQL

If you found this article helpful, feel free to share it or explore more SQL tips on Thousand Tech Blog.