MySQL CASE Statement: Definition, Syntax, and Examples

The CASE statement in MySQL is used to perform conditional logic in SQL queries, similar to IF-ELSE statements in programming. It evaluates conditions and returns a result based on the first condition that evaluates to TRUE.

Types of CASE Statements

Simple CASE → Compares a single expression to multiple values.
Searched CASE → Evaluates multiple conditions with logical expressions.

we have students table

students table

student_id	name	gender	city	age	score	course_id	email	phone_number
1	Alice	Female	Delhi	20	85	101	NULL	1234567890
2	Bob	Male	Mumbai	22	75	NULL	bob@email.com	NULL
3	Charlie	Male	Delhi	21	95	102	NULL	NULL
4	David	Male	Bangalore	23	65	101	david@email.com	NULL
5	Eve	Female	Mumbai	20	80	103	NULL	NULL
6	Frank	Male	Delhi	22	90	103	NULL	NULL
7	Alice	Female	Mumbai	19	60	102	NULL	NULL

1. Simple CASE Statement

Syntax:

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

Example

We can calculate grades using a Simple CASE statement based on the student’s score.

Example

SELECT name, score,

CASE score

WHEN 95 THEN 'A+'

WHEN 90 THEN 'A'

WHEN 80 THEN 'B'

WHEN 70 THEN 'C'

WHEN 60 THEN 'D'

ELSE 'F'

END AS grade

FROM students;

Try it yourself

Output:

name	score	grade
Alice	85	F
Bob	75	F
Charlie	95	A+
David	65	F
Eve	80	B
Frank	90	A
Alice	60	D

Explanation:

CASE score directly compares the score column with the specified values.
It returns the matching grade based on the value of score.
If no match is found, the ELSE clause returns 'F'.

2. Searched CASE Statement

Syntax:

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

Example:

We can calculate grades based on student scores using CASE.

Example

SELECT name, score,

CASE

WHEN score >= 90 THEN 'A'

WHEN score >= 80 THEN 'B'

WHEN score >= 70 THEN 'C'

WHEN score >= 60 THEN 'D'

ELSE 'F'

END AS grade

FROM students;

Try it yourself

Output:

name	score	grade
Alice	85	B
Bob	75	C
Charlie	95	A
David	65	D
Eve	80	B
Frank	90	A
Alice	60	D

Explanation:

CASE checks the score and assigns a grade.
NULL is treated as not matching any condition, so it defaults to 'F'.

❮ Previous

Next ❯

Whereisstuff is simple learing platform for beginer to advance level to improve there skills in technologies.we will provide all material free of cost.you can write a code in runkit workspace and we provide some extrac features also, you agree to have read and accepted our terms of use, cookie and privacy policy.