Two Forms of CASE Expression in MySQL: Syntax Differences and Proper Usage Guide

Introduction

In MySQL database queries, the CASE expression is a powerful tool for implementing conditional logic, widely used in scenarios such as data transformation, classification calculations, and dynamic column generation. However, many developers often confuse its two syntax forms in practice, leading to query results that deviate from expectations. This article systematically explains the correct usage of the CASE expression through a typical error case.

Error Case Analysis

Consider the following query example, which aims to return different calculation results based on the value of the base_price field:

SELECT
   CASE course_enrollment_settings.base_price
    WHEN course_enrollment_settings.base_price = 0      THEN 1
    WHEN course_enrollment_settings.base_price<101      THEN 2
    WHEN course_enrollment_settings.base_price>100 AND   
                      course_enrollment_settings.base_price<201 THEN 3
        ELSE 6
   END AS 'calc_base_price',
   course_enrollment_settings.base_price
FROM
    course_enrollment_settings
WHERE course_enrollment_settings.base_price = 0

The developer expects to return 1 when base_price = 0, but the actual execution always returns 3. This inconsistency stems from a misunderstanding of the syntax forms of the CASE expression.

Two Forms of CASE Expression

According to the MySQL official documentation, the CASE expression has two different syntax forms:

1. Simple CASE Expression

The syntax structure is as follows:

CASE case_value
    WHEN when_value THEN statements
    [WHEN when_value THEN statements]
    ELSE statements
END

This form compares case_value with each when_value for equality. If a match is found, the corresponding THEN statement is executed. In the error case, the developer incorrectly used this form by specifying course_enrollment_settings.base_price as case_value, while the conditional expressions in the WHEN clauses (e.g., course_enrollment_settings.base_price = 0) are evaluated and then compared to case_value. For example, when base_price = 0, the first WHEN clause condition evaluates to 1 (TRUE is typically represented as 1 in MySQL), but case_value is 0, so the comparison 0 = 1 fails, causing a logic error.

2. Searched CASE Expression

The syntax structure is as follows:

CASE
    WHEN <search_condition> THEN statements
    [WHEN <search_condition> THEN statements]
    ELSE statements
END

This form directly evaluates the search condition in each WHEN clause without specifying a case_value. When a condition is true, the corresponding THEN statement is executed. This is ideal for handling complex conditional logic.

Correct Implementation and Code Example

For the error case, the correct approach is to use the searched CASE expression. The modified query is as follows:

SELECT
   CASE
    WHEN course_enrollment_settings.base_price = 0      THEN 1
    WHEN course_enrollment_settings.base_price < 101      THEN 2
    WHEN course_enrollment_settings.base_price > 100 AND   
                      course_enrollment_settings.base_price < 201 THEN 3
        ELSE 6
   END AS 'calc_base_price',
   course_enrollment_settings.base_price
FROM
    course_enrollment_settings
WHERE course_enrollment_settings.base_price = 0

In this version, course_enrollment_settings.base_price after CASE is removed, and conditions are evaluated directly in the WHEN clauses. When base_price = 0, the first condition is true, returning 1, which meets expectations. Other answers further explain the error cause: in the original query, conditions are evaluated and then compared to case_value, leading to logical confusion. For example, base_price = 0 evaluates to 1, but case_value is 0, so they do not match.

Best Practices and Summary

When using the CASE expression in MySQL, it is recommended to follow these guidelines:

• For simple equality comparisons, use the simple CASE expression, ensuring that when_value is a constant or expression, not a condition.
• For scenarios involving range checks, multiple condition combinations, or complex logic, prefer the searched CASE expression to avoid syntax confusion.
• When writing queries, carefully inspect the part after CASE: if a value is specified, subsequent WHEN clauses should compare with it; otherwise, WHEN clauses should contain complete conditional expressions.
• Refer to official documentation for the latest syntax details and examples.

By understanding the differences between these two forms, developers can leverage the CASE expression more effectively, improving query accuracy and readability. In real-world projects, validating logic with test data helps detect and correct such errors early.