Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/sql/case-function-in-mysql/ below:

MySQL CASE() Function - GeeksforGeeks

Last Updated : 23 Jul, 2025

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.

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.

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;

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.

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.

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

First let's create a table:

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:

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  

• 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.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4