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.
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
Types of CASE Statements
Simple CASE
Compares a column/value with fixed values
Searched CASE
Uses logical conditions (>, <, BETWEEN, etc.)
Syntax – Simple CASE
Syntax – Searched CASE (Most Used)
Simple CASE Example
Searched CASE Example
Grading System
CASE with WHERE Clause
The CASE expression is used inside the WHERE clause to apply conditional filtering. It allows different conditions to be evaluated dynamically. The result of CASE is compared against a value in the WHERE clause.
Example
CASE with ORDER BY
CASE can control custom sorting logic in the ORDER BY clause. Rows are ordered based on conditional priority rather than natural sorting.
Example
CASE in UPDATE Statement
CASE can be used inside UPDATE to set different values based on conditions. Multiple conditional updates can be done in a single query.
Example
CASE vs IF() (Interview Favorite)
CASE handles multiple conditions and is ANSI-SQL standard. IF() handles only true or false conditions. CASE improves readability for complex logic. IF() is MySQL-specific and best for simple conditions.
CASE Example
IF() Example
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
