Order of when-clauses in case statement

Hi there,
I have reached the 1st SQL code challenge in the web development curriculum.
One of the exercises includes a case statement:
SELECT name,
when review > 4.5 then ‘Extraordinary’
when review > 4 then ‘Excellent’
when review > 3 then ‘Good’
when review > 2 then ‘Fair’
else ‘Poor’
end as ‘Grade’
from nomnom;

I had all the when clauses in reverse order before and noted that nearly all the restaurants had a grade of ‘Fair’. Just wanted to check and confirm, as to me, this means that the order does in fact matter, right? A restaurant with a rating of say ‘4’ will be evaluated against the first rule in the list, and if that rule says ‘greater than 2’, the restaurant satisfies the rule, and will get a rating of ‘Fair’, although that was not intended. I also assume that the rest of the clauses is not evaluated if a prior rule or clause delivers a match. Is that correct?

Thanks in advance,


Yes. CASE returns the value of the first clause it gets to in which the condition is met, and then does not evaluate any others.

1 Like