🔗 MySQL UNION ALL Operator

The UNION ALL operator in MySQL is used to combine the result sets of two or more SELECT queries and include all rows, even duplicates.

👉 Think of it as stacking results vertically without removing duplicates.

1️⃣ What is UNION ALL?

• Combines multiple SELECT results

• Keeps duplicate rows

• Faster than UNION (no duplicate check)

📌 All SELECT statements must have:

• Same number of columns

• Compatible data types

• Same column order

2️⃣ Basic Syntax

3️⃣ Simple Example ⭐

✔ Returns all names, including duplicates

4️⃣ UNION vs UNION ALL ⭐ (Very Important)

📌 Use UNION ALL when duplicates are acceptable or required.

5️⃣ UNION ALL with Multiple Columns

6️⃣ UNION ALL with WHERE Clause

📌 WHERE applies to each SELECT separately.

7️⃣ UNION ALL with ORDER BY ⭐

📌 ORDER BY must be applied at the end.

8️⃣ UNION ALL with Different Table Names (Aliases)

✔ Adds a source identifier column

9️⃣ UNION ALL with Aggregate Queries

🔟 Common Errors & Fixes ❌

📌 Interview Questions & MCQs (Very Important)

Q1. What does UNION ALL do?

A) Joins tables
B) Removes duplicates
C) Combines results including duplicates
D) Sorts data

✅ Answer: C

Q2. Which is faster?

A) UNION
B) UNION ALL

✅ Answer: B

Q3. Can UNION ALL remove duplicates?

A) Yes
B) No

✅ Answer: B

Q4. How many ORDER BY clauses are allowed?

A) One per SELECT
B) Only one at the end
C) None
D) Unlimited

✅ Answer: B

Q5. Can UNION ALL work with more than two SELECT statements?

A) Yes
B) No

✅ Answer: A

Q6. Column count rule in UNION ALL?

A) Any
B) Same count & compatible types
C) Same table
D) Same names only

✅ Answer: B

🔥 Real-Life Use Cases

✔ Combining yearly data
✔ Merging logs from multiple tables
✔ Appending partitioned tables
✔ Reporting across periods

✅ Summary

• UNION ALL combines SELECT results with duplicates

• Faster than UNION

• Requires same column count & compatible types

• ORDER BY goes at the end

• Essential for analytics & interviews