How does MYSQL control flow function CASE works?

MySQL CASE statement is a flow control function that allows us to build conditions inside a query such as SELECT or WHERE clause. We have two syntaxes of CASE statement

Syntax-1

CASE val
WHEN compare_val1 THEN result1
WHEN compare_val2 THEN result2
.
.
.
Else result
END

Here in this 1st syntax, if the val is equal to compare_val1 then the CASE statement returns result1. If the val is equal to compare_val2 then the CASE statement returns result2 and so on.

In case if the val does not match any compare_val then the CASE statement returns the result specified in ELSE clause.

Example

mysql> Select CASE 100
    -> WHEN 100 THEN 'It is matched'
    -> WHEN 200 THEN 'It is not matched'
    -> ELSE 'No use of this Number'
    -> END as 'Matching the Number';
+---------------------+
| Matching the Number |
+---------------------+
| It is matched       |
+---------------------+
1 row in set (0.06 sec)

Syntax-2

CASE
WHEN condition_1 THEN result1
WHEN condition_2 THEN result2
.
.
.
Else result
END

Here in this 2nd syntax, the CASE statement returns result1, result2, etc. if the condition is true. In case if all the conditions are false the CASE statement returns the result specified in ELSE clause.

Example

mysql> Select CASE
    -> WHEN (100 = 100) THEN 'It is Matched'
    -> WHEN (200 = 100) Then 'It is Not Matched'
    -> Else 'No use of Number'
    -> END as 'Matching the Number';
+---------------------+
| Matching the Number |
+---------------------+
| It is Matched       |
+---------------------+
1 row in set (0.00 sec)

Swarali Sree

e

Updated on: 11-Feb-2020

238 Views

Kickstart Your Career

Get certified by completing the course

Get Started