MySQL CASE Statement

🧠 MySQL CASE Statement

The CASE statement in MySQL is used to apply conditional logic inside SQL queries—similar to if–else in programming.

👉 It helps you return different values based on conditions.

---

1️⃣ What is CASE Statement?

• A conditional expression

• Evaluates conditions in order

• Returns the result of the first TRUE condition

• Ends with END

📌 Used inside SELECT, WHERE, ORDER BY, UPDATE

---

2️⃣ Types of CASE Statements

1️⃣ Simple CASE

Compares a column/value with fixed values

2️⃣ Searched CASE

Uses logical conditions (>, <, BETWEEN, etc.)

---

3️⃣ Syntax – Simple CASE

---

4️⃣ Syntax – Searched CASE ⭐ (Most Used)

---

5️⃣ Simple CASE Example

---

6️⃣ Searched CASE Example ⭐

Grading System

---

7️⃣ CASE with WHERE Clause

📌 Filters rows based on conditional logic

---

8️⃣ CASE with ORDER BY ⭐

✔ Custom sorting order

---

9️⃣ CASE in UPDATE Statement

---

🔟 CASE vs IF() ⭐ (Interview Favorite)

📌 Prefer CASE in interviews & production code.

---

1️⃣1️⃣ Common Mistakes ❌

❌ Forgetting END
❌ Wrong condition order
❌ Expecting CASE to stop after ELSE
❌ Using CASE when IF is simpler

---

📌 Interview Questions & MCQs (Very Important)

Q1. What is the purpose of CASE statement?

A) Looping
B) Conditional logic
C) Indexing
D) Joining tables

✅ Answer: B

---

Q2. Which keyword ends a CASE statement?

A) STOP
B) BREAK
C) END
D) FINISH

✅ Answer: C

---

Q3. CASE statement can be used in:

A) SELECT
B) WHERE
C) ORDER BY
D) All of the above

✅ Answer: D

---

Q4. Which CASE is more flexible?

A) Simple CASE
B) Searched CASE

✅ Answer: B

---

Q5. CASE is similar to which programming construct?

A) for loop
B) while loop
C) if–else
D) function

✅ Answer: C

---

Q6. Which is ANSI standard?

A) IF()
B) CASE

✅ Answer: B

---

Q7. What happens if no condition matches and ELSE is missing?

A) Error
B) NULL returned
C) 0 returned
D) Query fails

✅ Answer: B

---

🔥 Real-Life Use Cases

✔ Grading systems
✔ Salary classification
✔ Status mapping
✔ Custom sorting
✔ Report generation

---

✅ Summary

• CASE provides conditional logic in SQL

• Two types: Simple & Searched

• Used in SELECT, WHERE, ORDER BY, UPDATE

• More powerful & portable than IF()

• Very important for SQL exams & interviews