WS WhereIsStuff
Tutorial Guides
Log In Sign Up

Advertisement

Google Ad Slot: content-top

MySQL CASE

Previous Next

MySQL CASE Statement

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


  1. Simple CASE → Compares a single expression to multiple values.
  2. 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