How do I use properly CASE..WHEN in MySQL

Here is a demo query, notice it is very simple, Fetches only where base_price is 0, And still, it chooses the condition 3:

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 

base_price is decimal(8,0)

When run this on my DB, I get:

3 0
3 0
3 0
3 0
3 0

6 Answers

Remove the course_enrollment_settings.base_price immediately after CASE:

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

CASE has two different forms, as detailed in the manual. Here, you want the second form since you're using search conditions.

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

Or:

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

here CASE is an expression in 2nd scenario search_condition will evaluate and if no search_condition is equal then execute else

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

should be

SELECT CASE WHEN course_enrollment_settings.base_price = 0 THEN 1 

CASE course_enrollment_settings.base_price is wrong here, it should be just CASE

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 

Some explanations. Your original query will be executed as :

SELECT CASE 0 WHEN 0=0 THEN 1 -- condition evaluates to 1, then 0 (from CASE 0)compares to 1 - false WHEN 0<1 THEN 2 -- condition evaluates to 1,then 0 (from CASE 0)compares to 1 - false WHEN 0>100 and 0<201 THEN 3 -- evaluates to 0 ,then 0 (from CASE 0)compares to 0 - true ELSE 6, ... 

it's why you always get 3

SELECT CASE WHEN course_enrollment_settings.base_price = 0 THEN 1 WHEN course_enrollment_settings.base_price>0 AND course_enrollment_settings.base_price<=100 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 

There are two variants of CASE, and you're not using the one that you think you are.

What you're doing

CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list] ... [ELSE statement_list] END CASE 

Each condition is loosely equivalent to a if (case_value == when_value) (pseudo-code).

However, you've put an entire condition as when_value, leading to something like:

if (case_value == (case_value > 100)) 

Now, (case_value > 100) evaluates to FALSE, and is the only one of your conditions to do so. So, now you have:

if (case_value == FALSE) 

FALSE converts to 0 and, through the resulting full expression if (case_value == 0) you can now see why the third condition fires.

What you're supposed to do

Drop the first course_enrollment_settings so that there's no case_value, causing MySQL to know that you intend to use the second variant of CASE:

CASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list] ... [ELSE statement_list] END CASE 

Now you can provide your full conditionals as search_condition.

Also, please read the documentation for features that you use.

I think part of it is that you're stating the value you're selecting after CASE, and then using WHEN x = y syntax afterward, which is a combination of two different methods of using CASE. It should either be

CASE X WHEN a THEN ... WHEN b THEN ... 

or

CASE WHEN x = a THEN ... WHEN x = b THEN ...