Does the case expression short circuit?
8June 13, 2019 by Kenneth Fisher
Let’s start with definitions:
CASE Expression: This is basically an inline IF .. ELSE IF .. ELSE IF ….. etc statement. This is going to be similar to a SWITCH in other languages.
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.
SQL Studies 
>> CASE Statement: This is basically 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 <> Since this is inline you can’t just run this on its own, but you can put it in the field list,.. <<
You might want to read a book on SQL and learn the difference between a field and a column. A field is part of a column. The most common example is breaking a date into (year, month, day) fields, which we can extract with either proprietary functions in Microsoft SQL or with an extract ( FROM ) in standard SQL. Your example that includes an error in a WHEN clause should have set at least a warning in standard SQL, as part of the first powers to determine the data type of a whole expression.
Hmm I’d always thought that the list after the SELECT was called a field_list. I just looked in BOL though and it’s called a select_list there. I’ll have to start calling it that. If I understand what you are saying correctly though isn’t the select_list a combination of fields and columns? Although it also sounds like a column would be part of the set of fields? In other words if a field is some piece of a column then one would assume that the whole column is also considered a field.
Terminology aside I will say that this is very language dependent and that it’s definitely not going to follow standard SQL exactly.
Interesting read. It made me wonder if the same was true of an IN clause. I tried the following:
SELECT col1
FROM (VALUES (1),(2),(3)) myTable (col1)
WHERE col1 IN (1,2,3,7/0)
and was bet with a divide by zero error, so I reckon the IN clause doesn’t short-circuit. I took it a step further and tested the following statement:
SELECT col1
FROM (VALUES (1),(2),(3)) myTable (col1)
WHERE col1 < 4 OR col1 = 7/0
and surprisingly that did not through an error. So it would appear that the IN clause is merely there to make it easier on the author/coder and not the engine.
I have to tell you that has to be one of the oddest things I’ve ever seen. I really appreciate you sharing.
[…] recently did a post on the case expression short circuting and received a very very interesting comment from Chad Estes. He posted, among other things, this […]
I too thought this query and its results were interesting:
SELECT col1 FROM (VALUES (1),(2),(3)) myTable (col1) WHERE col1 IN (1,2,3,7/0)
So I looked at the query plan. In the query plan, the ORs are in reverse order: 7/0 first, then 3, etc.. Even more interesting (for us SQL nerds, anyway).
Hu. That is strange. It looks like it does them in reverse order no matter what.
I know what you are saying, but CASE is an expression and not flow control. It is a scalar value, not anything like IF-THEN-ELSE execution control.