Case Statement In MySQL

In MySQL, The CASE statement is used for conditional logic within SQL queries.

It allows us to perform conditional evaluations and return different results based on specified conditions.

Breakdown of how the CASE Statement works:

The CASE keyword initiates the conditional statement.
WHEN is used to specify a condition.
The condition is evaluated, and if it is true, the corresponding result is returned.
If none of the conditions is true, then the ELSE block gets executed.
The END keyword marks the end of the CASE statement.

CASE Statement Syntax:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    [ELSE else_result]
END

Use cases of CASE Statement in MySQL:

Simplifying Queries: Replace nested IF or IIF functions to make the query more readable.
Data Transformation: We can modify data values based on requirements and certain conditions.
Computed Columns: We can add new columns with values based on conditions.
Data Categorization: Transition data into categories based on specific criteria.
Conditional Aggregation: We can apply different aggregate functions on conditions.

Simplifying Queries using SELECT in MySQL:

In the below query, we have created columns with "mobile_id", "mobile_name", "ram_size" and we have added CASE Statement which will produces virtual column "min_price" using "mobiles" table.

Example:

SELECT 
    mobile_id, 
    mobile_name, 
    ram_size,
    CASE 
        WHEN ram_size >= 32 THEN 500
        WHEN ram_size >= 16 THEN 400
        WHEN ram_size >= 8 THEN 300
        WHEN ram_size >= 4 THEN 200
        ELSE 100
    END AS min_price
FROM mobiles;

in the above query, based on the "ram_size" of mobiles it will define min_price of mobile.

Data Transformation using SELECT in MySQL:

In the below query, we have created columns with "mobile_id", "mobile_name", "ram_size" and we have added CASE Statement which will produces virtual column "min_price" using "mobiles" table.

Example:

Data Transformation using SELECT in SQL:
SELECT 
    mobile_id, 
    mobile_name, 
    ram_size,
    CASE 
        WHEN ram_size >= 32 THEN 500
        WHEN ram_size >= 16 THEN 400
        WHEN ram_size >= 8 THEN 300
        WHEN ram_size >= 4 THEN 200
        ELSE 100
    END AS min_price
FROM mobiles;

in the above query, based on "ram_size" of mobiles we were displaying extra virtual column (min_price) in our table.

Computed Columns using SELECT in MySQL:

in the below query, based on "mobile_price" from mobiles table we were displaying extra virtual column ( mobile_category ) in our table.

Example:

SELECT 
    mobile_id, 
    mobile_name, 
    mobile_price,
    CASE 
        WHEN mobile_price >= 500 THEN 'Premium Mobile'
        ELSE 'Mid Range Mobiles'
    END AS mobile_category
FROM mobiles;

<-- Any All

Null Functions -->