How to Correctly Use CASE WHEN in MySQL with Numeric Columns and Search Conditions?

Proper Usage of CASE..WHEN in MySQL

In MySQL, the CASE..WHEN construct allows users to evaluate multiple conditions and return a corresponding result. However, a common issue arises when using CASE with search conditions and a numeric column.

Original Query and Issue:

In a query, the CASE statement evaluates the base_price column, where base_price is defined as decimal(8,0):

SELECT
   CASE course_enrollment_settings.base_price
    WHEN course_enrollment_settings.base_price = 0      THEN 1
    ...
    ELSE 6
   END AS 'calc_base_price',
   course_enrollment_settings.base_price
FROM
    course_enrollment_settings
WHERE course_enrollment_settings.base_price = 0;

The result of this query returns unexpected results where all selected rows, where base_price is 0, return the condition 3:

3 0
3 0
3 0
3 0
3 0

Solution:

To rectify this issue, the column specification after CASE should be removed. In the second form of CASE, the column name is not required after the word CASE if you use search conditions.

SELECT
   CASE
    WHEN course_enrollment_settings.base_price = 0      THEN 1
    ...
    ELSE 6
   END AS 'calc_base_price',
   course_enrollment_settings.base_price
FROM
    course_enrollment_settings
WHERE course_enrollment_settings.base_price = 0;

Explanation:

CASE has two different forms: the first form specifies the column name, while the second form does not. The correct usage of the second form when evaluating search conditions allows MySQL to correctly evaluate the conditions and return the intended results.

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