MySQL CASE() Function

MySQL CASE function is a conditional statement that returns a value when the first condition is met.

Once a condition is met, the CASE function does not check for other conditions. If no condition is met it returns the output in ELSE part.

CASE Function in MySQL

The CASE Function in MySQL allows using conditional logic within the queries. It evaluates the conditions and returns a value when a condition is met (like an if-then-else statement).

It can be used with different statements like SELECT, WHERE, and ORDER BY clause based on its use and requirements.

Some features of MySQL CASE function are shown below:

• This function returns the statement in the else part if none of the stated conditions are true.
• This function returns NULL if none of the stated conditions are true and there is no ELSE part.
• This function comes under Advanced Functions.
• This function accepts two parameters namely conditions and results.

Syntax

The CASE function syntax in MySQL is given below

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

Parameters:

This method accepts two parameters as given below:

• condition1, condition2, …conditionN: Specified conditions which are determined in the order they are stated.
• result1, result2, …resultN: Specified output which is to be returned if the stated condition is satisfied.

Returns:

It returns a value by passing over conditions whenever any condition satisfies the given statement otherwise it returns the statement in an else part. And if none of the conditions are satisfied as well as there is no else part then it returns NULL.

MySQL CASE Function Examples

To understand the working of MySQL CASE function, let’s look at some examples of CASE function.

First let’s create a table:

Demo MySQL Database

MySQL

CREATE TABLE float01001
(  
user_id int NOT NULL AUTO_INCREMENT,
float_val float,
PRIMARY KEY(user_id)
);
INSERT float01001(float_val)  
VALUES (1.9);

INSERT float01001(float_val)  
VALUES (1.1);

INSERT float01001(float_val)  
VALUES (3.9);

INSERT float01001(float_val)  
VALUES (5.0);

INSERT float01001(float_val)  
VALUES (10.9);

Now, we will check some CASE function queries with output base on this table:

Example 1: Using the CASE() function and getting the output.

SELECT float_val,
CASE
    WHEN float_val > 5 THEN "The value is greater than 5"
    WHEN float_val = 5 THEN "The value is 5"
    ELSE "The value is under 5"
END as float_txt
FROM float01001;

Output:

float_val | float_txt                   |
+-----------+-----------------------------+
|       1.9 | The value is under 5        |
|       1.1 | The value is under 5        |
|       3.9 | The value is under 5        |
|         5 | The value is 5              |
|      10.9 | The value is greater than 5 

Example 2:

Using the CASE() function and checking if the length of the stated float value is greater than, or less than, or equal to 4.

SELECT float_val,
CASE
    WHEN LENGTH(float_val) > 4 THEN "The length is greater than 4"
    WHEN LENGTH(float_val) = 4 THEN "The length is 4"
    ELSE "The length is less than 4"
END as float_txt
FROM float01001;

Output:

float_val | float_txt                 |
+-----------+---------------------------+
|       1.9 | The length is less than 4 |
|       1.1 | The length is less than 4 |
|       3.9 | The length is less than 4 |
|         5 | The length is less than 4 |
|      10.9 | The length is 4  

Important Points About MySQL CASE Function

• The MySQL CASE function is a conditional statement that returns a value when the first condition is met.
• Once a condition is satisfied, the CASE function stops evaluating further conditions.
• If no condition is met, the CASE function returns the output specified in the ELSE part.
• The CASE function can be used in various SQL statements like SELECT, WHERE, and ORDER BY to introduce conditional logic.
• It allows for the implementation of if-then-else logic within queries efficiently.
• The CASE function enhances query flexibility by enabling different result outputs based on specified conditions.