Operatons with CASE

When using CASE and inqualities in the WHEN part of the statement, why is it not necessary to use BETWEEN?

For Example…using the following code:
SELECT *,
CASE
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 ‘rating’
FROM nomnom ;

I would think it would cause a syntax eror because if a number is >4.5 it is also >4 and >3 and >2 so I am suprised the program knows which THEN statement to apply. Why do you not need to write the code as follows?

SELECT *,
CASE
WHEN review > 4.5 THEN ‘Extraordinary’
WHEN review > 4 AND <=4.5 THEN ‘Excellent’
WHEN review > 3 AND <=4 THEN ‘Good’
WHEN review > 2 AND <=3 THEN ‘Fair’
ELSE ‘Poor’
END AS ‘rating’
FROM nomnom ;

And what if the code was like this
SELECT *,
CASE
WHEN review > 4 THEN ‘Excellent’
WHEN review > 4.5 THEN ‘Extraordinary’
WHEN review > 2 THEN ‘Fair’
WHEN review > 3 THEN ‘Good’
ELSE ‘Poor’
END AS ‘rating’
FROM nomnom ;

Now that the review> are out of order would the compound statements be necessary?

The reason you don’t have to check for ‘between-ness’ is that the CASE block is exited as soon as a WHEN condition is satisfied. If the review is greater than 4.5, execution jumps to END and ‘Extraordinary’ is assigned AS 'rating'. In your third example, you would have to check for ‘between-ness’ since the conditions are not in numerical order. Following the pattern of your first example is a common strategy in programming to avoid having to use AND to check for ‘between-ness’ in every language. Hope this helps!

1 Like

Thank you. Does CASE work that way for every version of SQL?

I’m no authority on every version of SQL, but I would assume so. This behavior is common to every programming language I’m familiar with. It basically mimics the behavior of an
if..then..else if..else if..else block of code.