Does the case expression short circuit?

8

June 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.

8 thoughts on “Does the case expression short circuit?

  1. Joe Celko says:

    >> 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.

  2. Chad Estes says:

    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.

  3. […] 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 […]

  4. ScottPletcher says:

    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).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,528 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: