MySQL CASE operator

CASE operator

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

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: 5.6

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'; 

Sample 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';

Sample 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';

Sample 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)
 

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

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)

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

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)

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

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)