MySQL UNION Operator

MySQL Tutorial

🔗 MySQL UNION Operator

The UNION operator in MySQL is used to combine the result sets of two or more SELECT queries into a single result set, removing duplicate rows automatically.

👉 Think of UNION as vertical merging + duplicate removal.

1️⃣ What is UNION?

  • Combines results of multiple SELECT statements

  • Eliminates duplicate rows

  • Returns a distinct set of records

📌 Each SELECT must have:

  • Same number of columns

  • Compatible data types

  • Same column order

2️⃣ Basic Syntax


3️⃣ Simple Example ⭐


✔ Returns unique student names only
❌ Duplicate names appear once

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

Feature UNION UNION ALL
Duplicate rows ❌ Removed ✔ Kept
Performance Slower Faster
Implicit DISTINCT ✔ Yes ❌ No
Use case Unique records Complete data

📌 Use UNION when duplicates must be removed.

5️⃣ UNION with Multiple Columns


✔ Duplicate (id, name) rows are removed.

6️⃣ UNION with WHERE Clause


📌 WHERE applies to each SELECT individually.

7️⃣ UNION with ORDER BY ⭐


📌 ORDER BY must be written once at the end, not in each SELECT.

8️⃣ UNION with Literal / Alias Columns


✔ Helps identify source table.

9️⃣ UNION with Aggregate Queries


🔟 Common Errors & Fixes ❌

Error Cause Fix
Column count mismatch Different columns Match column count
Data type mismatch Incompatible types Use CAST()
ORDER BY error Placed before UNION Move ORDER BY to end

1️⃣1️⃣ UNION vs JOIN ⭐ (Interview)

Feature UNION JOIN
Combines Rows Columns
Direction Vertical Horizontal
Relationship needed ❌ No ✔ Yes

📌 Interview Questions & MCQs (Very Important)

Q1. What does UNION do?

A) Joins tables
B) Combines results removing duplicates
C) Sorts rows
D) Filters rows

✅ Answer: B

Q2. Does UNION remove duplicates?

A) Yes
B) No

✅ Answer: A

Q3. Which is faster?

A) UNION
B) UNION ALL

✅ Answer: B

Q4. How many ORDER BY clauses are allowed in UNION?

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

✅ Answer: B

Q5. Can UNION combine more than two SELECT statements?

A) Yes
B) No

✅ Answer: A

Q6. Column rule for UNION?

A) Same names only
B) Same number & compatible types
C) Same table
D) Same primary key

✅ Answer: B

Q7. Which removes duplicates automatically?

A) UNION
B) UNION ALL

✅ Answer: A

🔥 Real-Life Use Cases

✔ Merging yearly/partitioned data
✔ Combining reports from multiple tables
✔ Creating consolidated views
✔ Removing duplicates across datasets

✅ Summary

  • UNION combines multiple SELECT results

  • Automatically removes duplicate rows

  • Slower than UNION ALL

  • Requires same column count & compatible types

  • ORDER BY applies at the end

  • Very important for SQL exams & interviews

You may also like...