MySQL CASE Statement

The CASE statement in MySQL is a control flow structure used to create a sequence of conditions. It allows you to perform conditional logic in SQL queries by executing different sequences for different conditions, similar to if-else or switch-case statements in programming languages. The CASE statement can be used in any statement or clause that allows a valid expression. 

Syntax: 

The CASE statement has two formats:

Simple CASE:

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    [ELSE default_result]
END

Searched CASE:

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

Demo Database

For our demonstration, we'll use the following table:

orders:

order_id	product	quantity
1	Laptop	20
2	Phone	15
3	Tablet	10

Examples

Using Simple CASE: 

Determine the category based on the product:

SELECT order_id, product,
CASE product
    WHEN 'Laptop' THEN 'Electronics'
    WHEN 'Phone' THEN 'Mobile'
    ELSE 'Other'
END AS product_category
FROM orders;

Using Searched CASE: 

Categorize order based on quantity:

SELECT order_id, product, quantity,
CASE
    WHEN quantity < 15 THEN 'Low Quantity'
    WHEN quantity BETWEEN 15 AND 20 THEN 'Medium Quantity'
    ELSE 'High Quantity'
END AS quantity_category
FROM orders;

Summary

The MySQL CASE statement offers a versatile way to introduce conditional logic directly within your SQL queries, allowing for dynamic results based on varying conditions. It's essential for tasks like data transformation, data analysis, and generating reports. 

By understanding how to use both the simple and searched CASE formats, developers can create more expressive and dynamic SQL statements.