June 13, 2019 by Kenneth Fisher
Let’s start with definitions:
CASE WHEN boolean expression THEN value WHEN boolean expression THEN value ... ELSE value END
Since this is inline you can’t just run this on its own, but you can put it in the field list, WHERE clause, HAVING clause, etc. Heck, you can even use it in the ORDER BY clause. Really you can replace any value with a case expression in pretty much any command.
Short Circuit: This means that if one of the boolean expressions evaluates to true then it doesn’t bother evaluating the rest of them.
A command short-circuiting is somewhat language and command dependent. So will a CASE expression short circuit? Let’s find out!
I had a number of different thoughts on how to prove this but my first, and simplest option ended up doing the trick.
SELECT CASE WHEN 1=1 THEN 'Yes' WHEN 1/0 = 4 THEN 'No' END;
The result is a Yes with no error. So the CASE expression stops evaluating after the first positive result. Also, it looks like the boolean expressions aren’t checked by the parser when the query is checked. Now, I should point out that if you have this in a SELECT statement then the CASE will potentially be evaluated multiple times and can run you into an error even after you’ve gotten some data. So for example:
SELECT CASE WHEN Col1/Col2 > 2 THEN 'More than 2' ELSE 'Less than 2' END FROM (VALUES (1,1), (4,1), (2,0), (6,2)) MyTable (Col1, Col2);
This will return (ignoring the fact that without an ORDER BY you have no guarantee of the order) two rows of data and then an error.
So why do we care about any of this? Two reasons. First, it’s really helpful when debugging to understand what’s actually happening and why you might be getting an error. Second, if you are careful, you can do some odd logic tasks when you understand exactly how something works.