SQL WHERE order of execution

I have another question regarding the exercise 11 on for the project ‘New York Restaurants ‘ in SQL Basic course.
Use a CASE statement to change the rating system to:

  • review > 4.5 is Extraordinary
  • review > 4 is Excellent
  • review > 3 is Good
  • review > 2 is Fair
  • Everything else is Poor

How does SQL WHERE order operate? Is it based on the order of the conditions (and parentheses )?

As in, if the review is 4.1 it will be higher 2 and 3 … so it is true for Fair, Good and Excellent… instead I would have written the code

when review > 2 and review <3 then ‘Fair’
when review > 3 and review <4 then ‘Good’
when review > 4 and review <4.5 then ‘Excellent’
when review > 4.5 then ‘Extraordinary’
else ‘Poor’

can someone tell me how sql execute the code?
Thank you for your help

Semhar

you have a question about WHERE while you use CASE?

its from right to left, top to bottom like you expect, and parentheses first

no, once a case condition is met, the remaining cases are no longer executed, see docs:

https://dev.mysql.com/doc/refman/5.7/en/case.html

3 Likes

Got it, thank you for your clarification.

1 Like