🔀 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 UNION and UNION 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 SELECT queries 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 UNION to remove duplicates
• Use UNION ALL to keep them
• Ensure matching columns and data types
• Final ORDER BY sorts 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 :