Conditional expressions: CASE <expression> WHEN ... THEN ... ELSE ... END

If you already know about CASE, it’s a way to embed logic right into your query. But not everyone knows that CASE comes in two flavors. If CASE WHEN ... is like an if-else check, then simple CASE is like switch-case from other languages: it compares one expression to several values and runs the matching block.

It looks more compact and intuitive when you’re comparing one field to fixed values.

Simple CASE syntax

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

It’s super simple:

• expression — this is the expression or field you’re comparing to value1, value2, etc.
• THEN tells what to return if there’s a match.
• ELSE — what to return if nothing matched (you can skip it, but it’s better not to).

Example: Student grades

Imagine a students table with a grade column, holding an integer from 2 to 5. We want to show a text version of the grades.

SELECT
    name,
    grade,
    CASE grade
        WHEN 5 THEN 'Excellent'
        WHEN 4 THEN 'Good'
        WHEN 3 THEN 'Satisfactory'
        WHEN 2 THEN 'Unsatisfactory'
        ELSE 'Unknown'
    END AS grade_in_words
FROM students;

Here, CASE compares the grade field to the fixed values 2, 3, 4, 5.

Example: Product categories by code

In the products table there’s a category_code field with short tags: 'el', 'frn', 'bks', etc. We want to show a readable category name.

SELECT
    product_name,
    category_code,
    CASE category_code
        WHEN 'el' THEN 'Electronics'
        WHEN 'frn' THEN 'Furniture'
        WHEN 'bks' THEN 'Books'
        ELSE 'Other'
    END AS category_name
FROM products;

This trick is especially handy when you want to show "human" text instead of a code.

Example: Month names

Sometimes you want to show month names as text by their number:

SELECT
    EXTRACT(MONTH FROM order_date) AS month_number,
    CASE EXTRACT(MONTH FROM order_date)
        WHEN 1 THEN 'January'
        WHEN 2 THEN 'February'
        WHEN 3 THEN 'March'
        WHEN 4 THEN 'April'
        WHEN 5 THEN 'May'
        WHEN 6 THEN 'June'
        WHEN 7 THEN 'July'
        WHEN 8 THEN 'August'
        WHEN 9 THEN 'September'
        WHEN 10 THEN 'October'
        WHEN 11 THEN 'November'
        WHEN 12 THEN 'December'
        ELSE 'Unknown'
    END AS month_name
FROM orders;

Here, simple CASE makes it clear what would otherwise just look like numbers from 1 to 12.

Working with NULL in simple CASE

It’s important to know that if you compare a value to NULL, the result will be NULL, because NULL = NULL is not true, it’s unknown.

Example with NULL:

SELECT
    user_id,
    status,
    CASE status
        WHEN 'active' THEN 'Active'
        WHEN 'blocked' THEN 'Blocked'
        WHEN NULL THEN 'No status'  -- won’t work!
        ELSE 'Unknown'
    END AS user_state
FROM users;

Instead of WHEN NULL you should use ELSE, or just use a searched CASE (which we covered in the last lecture):

CASE
    WHEN status IS NULL THEN 'No status'
    ...

Comparison with searched CASE

Practical cases

Example: Visualizing request status

SELECT
    request_id,
    CASE status
        WHEN 'new' THEN '🟡 New'
        WHEN 'in_progress' THEN '🔵 In progress'
        WHEN 'done' THEN '🟢 Completed'
        ELSE '⚪ Unknown'
    END AS status_label
FROM requests;

Here CASE acts like a translator: it turns technical statuses into clear (and even cute) labels. And if the status is unknown or NULL — the user gets ⚪ “Unknown”.

Example: Grouping by category for a report

SELECT
    CASE department
        WHEN 'HR' THEN 'HR'
        WHEN 'IT' THEN 'Tech'
        WHEN 'FIN' THEN 'Finance'
        ELSE 'Other'
    END AS dept_name,
    COUNT(*) AS staff_count
FROM employees
GROUP BY
    CASE department
        WHEN 'HR' THEN 'HR'
        WHEN 'IT' THEN 'Tech'
        WHEN 'FIN' THEN 'Finance'
        ELSE 'Other'
    END;

Result:

What’s happening here:

• Employees from the HR department go into the HR category.
• IT — that’s Tech.
• FIN — that’s Finance.
• Everything else, including SALES and NULL, goes into Other. This approach is great for making friendly and clear reports.

Common mistakes

• Forgot ELSE → you get NULL if nothing matched.
• Comparing to NULL → doesn’t work, use IS NULL and searched CASE.
• Comparing incompatible types → for example, CASE grade WHEN '5' if grade is a number type.

Simple CASE is your tool when you need to compare one value to a set of possible ones. It’s compact, readable, and especially useful for text classification, visualization, code conversion, and easy grouping.

But if you need to check ranges, NULL, or more complex conditions — go for searched CASE. SQL loves clarity. And CASE is your tool to give that clarity some shape.