MySQL CASE operator

CASE operator

In MySQL, the CASE statement is used to apply a complex conditional construct in a stored program.

This function is useful in -

• It allows you to implement conditional logic directly within a query.
• This is invaluable for handling different scenarios and producing different results based on specified conditions.
• CASE can be used to transform data on-the-fly. For example, you can use it to categorize or label data based on specific criteria.
• You can use CASE to dynamically select different columns based on specific conditions.
• CASE allows you to set up contingency plans in case certain conditions are not met.
• It's crucial in generating customized reports where you want to format or label data differently based on certain criteria.
• By using CASE to streamline data processing, you can sometimes avoid the need for more complex joins or subqueries, which can lead to more efficient queries.

Syntax:

CASE value WHEN [compare_value] THEN result 
[WHEN [compare_value] THEN result ...] 
[ELSE result] 
END

OR

CASE WHEN [condition] THEN result 
[WHEN [condition] 
THEN result ...] 
[ELSE result] 
END

• The first syntax returns the result where value=compare_value.
• The second syntax returns the result for the first condition that is true.
• The list of corresponding SQL statements will execute when a search condition evaluates to true.
• The statement list in ELSE part will execute when no search condition matches.
• If there is no matching value found in the ELSE part, NULL will be returned.
• Each statement list can contain one or more statements and no empty statement list is allowed.

MySQL Version: 8.0

The return type of a CASE expression is the compatible aggregated type of all return values and depends on the context in which it is used. If used in a numeric context, the result is returned as a decimal, real, or integer value. If used in a string context, the result is returned as a string.

Note: The syntax of the CASE expression shown here differs slightly from that of the SQL CASE statement, CASE Syntax, for use inside stored programs. The CASE statement cannot have an ELSE NULL clause, and it is terminated with END CASE instead of END.

Example: MySQL CASE operator

In the following statement, CASE is 1, therefore "this is case one" is returned.

Code:

SELECT CASE 1 WHEN 1 THEN 'this is case one'
WHEN 2 THEN 'this is case two' 
ELSE 'this is not in the case'
END as 'how to execute case statement'; 

Output:

mysql> SELECT CASE 1 WHEN 1 THEN 'this is case one'
    -> WHEN 2 THEN 'this is case two' 
    -> ELSE 'this is not in the case'
    -> END as 'how to execute case statement';
+-------------------------------+
| how to execute case statement |
+-------------------------------+
| this is case one              | 
+-------------------------------+
1 row in set (0.00 sec)

Example: MySQL CASE operator when not matching

In the following statement CASE is 4, therefore it returns the ELSE part, i.e. 'this is not in the case'.

Code:

SELECT CASE 4 WHEN 1 THEN 'this is case one'         
WHEN 2 THEN 'this is case two'
ELSE 'this is not in the case'
END as 'how to execute case statement';

Output:

 mysql> SELECT CASE 4 WHEN 1 THEN 'this is case one'         
    -> WHEN 2 THEN 'this is case two'
    -> ELSE 'this is not in the case'
    -> END as 'how to execute case statement';
+-------------------------------+
| how to execute case statement |
+-------------------------------+
| this is not in the case       | 
+-------------------------------+
1 row in set (0.00 sec)

Example : MySQL CASE operator when matching

In the following statement, CASE is 2, therefore it returns 'this is case two'.

Code:

SELECT CASE 2 WHEN 1 THEN 'this is case one' 
WHEN 2 THEN 'this is case two'           
ELSE 'this is not in the case'
END as 'how to execute case statement';

Output:

mysql> SELECT CASE 2 WHEN 1 THEN 'this is case one'
    -> WHEN 2 THEN 'this is case two'
    -> ELSE 'this is not in the case'
    -> END as 'how to execute case statement';
+-------------------------------+
| how to execute case statement |
+-------------------------------+
| this is case two              |
+-------------------------------+
1 row in set (0.00 sec)
 

Example : MySQL CASE operator using greater than operator

In the following example, as the condition 2>3 is FALSE, it returns the ELSE part, i.e. 'this is false'.

Code:

SELECT CASE  WHEN 2>3 THEN 'this is true'
ELSE 'this is false' END; 

Output:

mysql> SELECT CASE  WHEN 2>3 THEN 'this is true' ELSE 'this is false' END; 
+-------------------------------------------------------------+
| CASE  WHEN 2>3 THEN 'this is true' ELSE 'this is false' END |
+-------------------------------------------------------------+
| this is false                                               | 
+-------------------------------------------------------------+
1 row in set (0.00 sec)

Example : MySQL CASE operator with less than operator

In the following example as the condition 2<3 is TRUE, therefore it returns 'this is true'.

Code:

SELECT CASE  WHEN 2< 3 THEN 'this is true'
ELSE 'this is false' END; 

Output:

mysql> SELECT CASE  WHEN 2< 3 THEN 'this is true'ELSE 'this is false' END;
+-------------------------------------------------------------+
| CASE  WHEN 2< 3 THEN 'this is true'ELSE 'this is false' END |
+-------------------------------------------------------------+
| this is true                                                | 
+-------------------------------------------------------------+
1 row in set (0.00 sec)

Example : MySQL CASE operator with no matching

In the following example, CASE is not satisfied by either of the WHEN, it returns NULL.

Code:

SELECT CASE BINARY 'A' WHEN 'a' THEN 1
   WHEN 'b' THEN 2 END; 
   
   

Output:

mysql> SELECT CASE BINARY 'A' WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
+-----------------------------------------------------+
| CASE BINARY 'A' WHEN 'a' THEN 1 WHEN 'b' THEN 2 END |
+-----------------------------------------------------+
|                                                NULL | 
+-----------------------------------------------------+
1 row in set (0.00 sec)

Previous: XOR operator
Next: IF()