CASE Query Statement

Function

This statement is used to obtain the value of boolean_expression for each WHEN statement in a specified order. Then return the first result_expression with the value TRUE of boolean_expression.

Syntax

1

CASE WHEN boolean_expression THEN result_expression [...n] [ELSE else_result_expression] END;

Keyword

boolean_expression: can include subquery. However, the return value of boolean_expression can only be of Boolean type.

Precautions

If there is no Boolean_expression with the TRUE value, else_result_expression will be returned when the ELSE clause is specified. If the ELSE clause is not specified, NULL will be returned.

Example

To query the student table and return the related results for the name and score fields: EXCELLENT if the score is higher than 90, GOOD if the score ranges from 80 to 90, and BAD if the score is lower than 80, run the following statement:

1

SELECT name, CASE WHEN score >= 90 THEN 'EXCELLENT' WHEN 80 < score AND score < 90 THEN 'GOOD' ELSE 'BAD' END AS level FROM student;