Let’s start with definitions:
CASE Statement: This is basically an inline IF .. ELSE IF .. ELSE IF ….. etc statement. In other languages you might see it called a switch statement.
CASE WHEN boolean statement THEN value WHEN boolean statement 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 statement in pretty much any command.
Short Circuit: This means that if one of the boolean statements 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 statement 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 statement stops evaluating after the first positive result. Also, it looks like the boolean statements 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.