CASE WHEN expression in MySQL

Introduction

The CASE WHEN expression helps you choose different results based on conditions, like making decisions in your data.

You want to label data based on values, like marking scores as 'Pass' or 'Fail'.

You need to create new categories from existing data without changing the original table.

You want to replace complex multiple IF statements with a cleaner way to check conditions.

You want to show different text or numbers depending on the data in each row.

You want to summarize data with custom groups in a query result.

Syntax

MySQL

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

The CASE expression checks each WHEN condition in order and returns the result for the first true condition.

The ELSE part is optional; if no conditions match and ELSE is missing, the result is NULL.

Examples

This example labels each exam score as 'Pass' if 60 or more, otherwise 'Fail'.

MySQL

SELECT
  CASE
    WHEN score >= 60 THEN 'Pass'
    ELSE 'Fail'
  END AS result
FROM exams;

This example uses CASE with simple value matching to name product categories.

MySQL

SELECT
  product_name,
  CASE category_id
    WHEN 1 THEN 'Books'
    WHEN 2 THEN 'Electronics'
    ELSE 'Other'
  END AS category_name
FROM products;

This example groups employees by salary ranges.

MySQL

SELECT
  employee_name,
  salary,
  CASE
    WHEN salary < 3000 THEN 'Low'
    WHEN salary BETWEEN 3000 AND 7000 THEN 'Medium'
    ELSE 'High'
  END AS salary_level
FROM employees;

Sample Program

This creates a simple table of exam scores and uses CASE WHEN to mark each as Pass or Fail.

MySQL

CREATE TABLE exams (id INT, score INT);
INSERT INTO exams VALUES (1, 55), (2, 75), (3, 40);

SELECT id, score,
  CASE
    WHEN score >= 60 THEN 'Pass'
    ELSE 'Fail'
  END AS result
FROM exams;

Important Notes

CASE WHEN expressions can be used in SELECT, WHERE, ORDER BY, and other SQL clauses.

You can nest CASE expressions inside each other for more complex logic.

Always test your CASE logic with sample data to ensure it works as expected.

Summary

CASE WHEN lets you return different results based on conditions in your data.

It works like an IF-THEN-ELSE decision inside SQL queries.

Use it to create readable, flexible queries that handle multiple conditions.