Here is MySQL CASE Statement Tutorial

The CASE statement in MySQL allows you to perform conditional logic inside SQL queries — like an IF-ELSE or SWITCH statement in programming languages.

---

✅ Syntax

✅ Simple CASE

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE default_result
END

✅ Searched CASE

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

You can use CASE in SELECT, ORDER BY, WHERE, GROUP BY, and even in UPDATE statements.

---

🎓 Example Table: students

id	name	marks
1	Alice	85
2	Bob	60
3	Carol	40
4	David	72
5	Emma	30

---

🔍 Example 1: Assign Grades Using CASE

SELECT name, marks,
  CASE
    WHEN marks >= 80 THEN 'A'
    WHEN marks >= 60 THEN 'B'
    WHEN marks >= 40 THEN 'C'
    ELSE 'F'
  END AS grade
FROM students;

Result:

name	marks	grade
Alice	85	A
Bob	60	B
Carol	40	C
David	72	B
Emma	30	F

---

🔍 Example 2: Use CASE in ORDER BY

SELECT name, marks
FROM students
ORDER BY
  CASE
    WHEN marks >= 40 THEN 1
    ELSE 2
  END,
  marks DESC;

Explanation:

• Pass students (marks ≥ 40) appear first
• Then fail students (marks < 40)

---

🔄 Example 3: Use CASE in UPDATE

UPDATE students
SET marks = 
  CASE
    WHEN marks < 35 THEN 35
    ELSE marks
  END;

This will boost all failing students to at least 35 marks.

---

⚖️ CASE vs IF

Feature	CASE	IF
Portability	More standard SQL	MySQL-specific
Multiple conditions	✅	❌ (only 2 branches)
Use in SELECT, ORDER BY	✅	✅
Readability	Better for many conditions	Short & simple for binary

---

📝 Best Practices

• Use CASE for clean, readable logic in SELECT, ORDER BY, etc.
• Prefer searched CASE for complex logical expressions
• Always include an ELSE for safety (optional but recommended)

---

🎯 Summary

Use Case	Example
Add a derived column	SELECT ..., CASE WHEN ... THEN ... END AS ...
Filter/order rows conditionally	ORDER BY CASE ...
Update with logic	UPDATE ... SET col = CASE ...