How Can I Correctly Use MySQL's CASE..WHEN Statement to Avoid Unexpected Results?

Understanding the CASE..WHEN Statement in MySQL

When working with MySQL, the CASE..WHEN statement is a valuable tool for evaluating multiple conditions and returning different values based on those conditions. However, there are specific ways to use it properly to avoid unexpected results.

Consider the following 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

The query aims to fetch courses with a base price of zero and conditionally assign a value to 'calc_base_price' based on price ranges. However, the result returns '3' for all rows with base_price 0.

To fix this, it is crucial to remove the column name after CASE:

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

This ensures that the CASE statement evaluates the conditions correctly and returns the appropriate value for each row. The CASE statement has two forms: one with a target column name and one without. In this scenario, the second form, which uses search conditions, is desired.

The above is the detailed content of How Can I Correctly Use MySQL's CASE..WHEN Statement to Avoid Unexpected Results?. For more information, please follow other related articles on the PHP Chinese website!