CASE WHEN in MySQL with Multiple Conditions

In this tutorial, we are going to see how to use CASE expression in MySQL with syntax and examples. CASE expression allows you to add if-else logic to a query. In general, you can use CASE expression anywhere, for example in SELECT, WHERE and ORDER BY clauses.
 

Syntax 1: CASE WHEN in MySQL with Multiple Conditions

CASE value
   WHEN value1 THEN instruction1
   WHEN value2 THEN instruction2
   …
   [ELSE instruction3]
END

In this syntax, CASE matches ‘value’ with “value1”, “value2”, etc., and returns the corresponding statement. If ‘value’ is not equal to any values CASE returns the statement in ELSE clause if ELSE clause is specified.
 

 
Example:

SELECT
  CASE level
    WHEN 'A' THEN 'Beginner'
    WHEN 'B' THEN 'Junior'
    WHEN 'C' THEN 'Senior'
    ELSE 'Level doesn`t exist!'
  END AS experience_levels
FROM company;

 
 

Syntax 2: CASE WHEN in MySQL with Multiple Conditions

CASE
   WHEN condition1 THEN instruction1
   WHEN condition2 THEN instruction2
   …
   [ELSE instruction3]
END

In this syntax, CASE evaluates the conditions specified in WHEN clauses. If a condition evaluates to true. CASE returns the corresponding statement in THEN clause. Otherwise, the statement specified in ELSE clause is returned. If ELSE clause does not exist, CASE expression returns NULL.
 

 
Example:

SELECT
  CASE
    WHEN points < 10 THEN 'failure'
    WHEN points BETWEEN 10 AND 12 THEN 'passable'
    WHEN points BETWEEN 12 AND 16 THEN 'good'
    ELSE 'very good'
  END AS result
FROM test;

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More