How to Avoid Ambiguity and Correctly Use CASE..WHEN Statements in MySQL?

Complexities of CASE..WHEN in MySQL

The CASE..WHEN statement in MySQL provides a versatile method for conditional expression evaluation. However, complexities arise when working with certain scenarios.

Ambiguous CASE Syntax

One such complexity is encountered when the base_price column is prefixed within the CASE expression. As seen in the example query, using course_enrollment_settings.base_price within the WHEN clauses introduces ambiguity.

Query:

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

Issue:

In this query, the CASE statement is incorrectly formulated. By including course_enrollment_settings.base_price after WHEN, it leads to an incorrect evaluation of conditions.

Resolution:

To resolve this issue, eliminate the table prefix within the CASE expression:

SELECT
   CASE
    WHEN base_price = 0      THEN 1
    ...
    END

Two Forms of CASE

The CASE statement has two distinct forms:

• Simple CASE: Used for simple conditional expressions involving equality checks.
• Searched CASE: Employs search conditions as in the provided example. In this form, the prefix within the WHEN clauses should be omitted.

By using the correct form and removing the redundant prefix, the CASE expression accurately evaluates the conditions.

The above is the detailed content of How to Avoid Ambiguity and Correctly Use CASE..WHEN Statements in MySQL?. For more information, please follow other related articles on the PHP Chinese website!