How to Correctly Use CASE...WHEN Statements in MySQL?

How do I use properly CASE..WHEN in MySQL

When working with CASE..WHEN statements in MySQL, it's essential to understand its correct usage to avoid unexpected results.

In the provided query, the issue stems from the inclusion of course_enrollment_settings.base_price immediately after CASE. For proper operation, you should remove this expression.

MySQL supports two forms of the CASE statement:

• Format 1 (Simple CASE): Used for simple comparisons without search conditions.

  • Example: CASE WHEN condition THEN result END

• Format 2 (Search Condition CASE): Used for search conditions and complex expressions.

  • Example: CASE WHEN condition THEN result ELSE result2 END

In the given query, you're using search conditions, so you require the second format. By removing course_enrollment_settings.base_price, the CASE statement becomes:

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

With this correction, the query will now produce the intended results.

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