Set Operations

Excellent 👍
Now SET OPERATIONS in the exact SAME pattern you’ve liked throughout:

• Base tables

• Question

• SQL

• Actual output table shown

• ✔ clear takeaways

---

📘 Base Tables (Used for ALL Set Operations)

customers

first_name	last_name
Amit	Kumar
Neha	Singh
Ravi	Verma
Suman	Gupta

---

employees

first_name	last_name
Neha	Singh
Ravi	Verma
Pooja	Sharma

---

🧾 Summary Table (Set Operation Questions)

No.	Command	Question
1	UNION	All unique names from both tables
2	INTERSECT	Common names in both tables
3	EXCEPT	Names in customers but not employees

---

1️⃣ UNION

Question: Show all unique first and last names from customers and employees.

SELECT first_name, last_name
FROM customers
UNION
SELECT first_name, last_name
FROM employees;

✅ Output

first_name	last_name
Amit	Kumar
Neha	Singh
Ravi	Verma
Suman	Gupta
Pooja	Sharma

✔ Combines results from both tables
✔ Removes duplicate rows automatically

---

2️⃣ INTERSECT

Question: Show names that exist in both customers and employees tables.

SELECT first_name, last_name
FROM customers
INTERSECT
SELECT first_name, last_name
FROM employees;

✅ Output

first_name	last_name
Neha	Singh
Ravi	Verma

✔ Returns only common rows
✔ Order and data types must match

---

3️⃣ EXCEPT

Question: Show names that are present in customers but not in employees.

SELECT first_name, last_name
FROM customers
EXCEPT
SELECT first_name, last_name
FROM employees;

✅ Output

first_name	last_name
Amit	Kumar
Suman	Gupta

✔ Left table minus right table
✔ Removes matching rows

---

🔥 UNION vs INTERSECT vs EXCEPT (Exam Favorite)

Feature	UNION	INTERSECT	EXCEPT
Combines rows	✅	❌	❌
Returns common rows	❌	✅	❌
Returns left-only rows	❌	❌	✅
Removes duplicates	✅	✅	✅

---

⚠️ Very Important Rules (Exams)

✔ Same number of columns
✔ Same data types
✔ Same column order

---

🧠 One-Line Memory Trick (Set Operations)

• UNION → All unique

• INTERSECT → Only common

• EXCEPT → Left minus right

---

⚠️ MySQL Note (Very Important)

• MySQL ❌ does NOT support INTERSECT & EXCEPT

• Alternatives:

  • INTERSECT → INNER JOIN

  • EXCEPT → LEFT JOIN + WHERE NULL

---

If you want next:

• MySQL alternatives with output

• MCQs: predict the output

• All SQL commands combined chart

• Hindi explanation (exam-oriented)

Just say 👍