How to Use the CASE Statement in MySQL

Contents
In this article, you will learn how to use the CASE statement in MySQL.
Using the CASE statement in MySQL
The CASE statement in MySQL is a powerful tool for creating conditional expressions. It allows you to define conditions and return different values based on those conditions.
Syntax
The basic syntax of the CASE statement in MySQL is as follows:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
WHEN conditionN THEN resultN
ELSE default_result
END;
In this syntax, “condition1” through “conditionN” are the conditions to be tested, and “result1” through “resultN” are the values to be returned if the corresponding condition is true. “default_result” is the value to be returned if none of the conditions are true.
Examples
Suppose we have a table called “students” with columns “id”, “name”, “gender”, and “age”. We want to create a new column called “age_group” that categorizes students based on their age.
SELECT id, name, age,
CASE
WHEN age < 18 THEN 'Under 18'
WHEN age >= 18 AND age < 25 THEN '18-24'
WHEN age >= 25 AND age < 35 THEN '25-34'
ELSE '35 and over'
END AS age_group
FROM students;
In this example, we are using the CASE statement to categorize students based on their age. The result set will include a new column called "age_group" with values such as "Under 18", "18-24", "25-34", and "35 and over".
And suppose we have a table called "orders" with columns "id", "customer_id", "order_date", and "total_price". We want to create a new column called "order_type" that categorizes orders as "small" or "large" based on the total price.
SELECT id, customer_id, order_date, total_price,
CASE
WHEN total_price < 100 THEN 'Small'
ELSE 'Large'
END AS order_type
FROM orders;
In this example, we are using the CASE statement to categorize orders as "Small" or "Large" based on the total price. The result set will include a new column called "order_type" with values such as "Small" or "Large".
And suppose we have a table called "employees" with columns "id", "name", "gender", "salary", and "department". We want to create a new column called "bonus" that calculates a bonus amount based on the salary and department.
SELECT id, name, gender, salary, department,
CASE department
WHEN 'Sales' THEN salary * 0.1
WHEN 'Marketing' THEN salary * 0.05
ELSE 0
END AS bonus
FROM employees;
In this example, we are using the CASE statement to calculate a bonus amount based on the salary and department. The result set will include a new column called "bonus" with values based on the department, such as 10% of the salary for the "Sales" department or 5% of the salary for the "Marketing" department.