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.
Comments
Post a Comment