How to Use the CASE Statement in MySQL

08/04/2021

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.

#MySQL