A curious case of case when and Null values

For some reason I have always written my Case statements using the following logic:

SELECT
CASE
	WHEN @Priority = 1 THEN 'Very High'
	WHEN @Priority = 2 THEN 'High'
	WHEN @Priority = 3 THEN 'Medium'
	WHEN @Priority = 4 THEN 'Low'
	ELSE 'N/A'
END

 

It wasn’t until recently that I noticed that there is another syntax that can be used with the CASE expression, the interesting part about this other form is that it is rather aesthetically pleasing when dealing with multiple WHEN clauses (or at least this is what I though at first 😛 )

 

Example:

SELECT
CASE @Priority
	WHEN 1 THEN 'Very High'
	WHEN 2 THEN 'High'
	WHEN 3 THEN 'Medium'
	WHEN 4 THEN 'Low'
	ELSE 'N/A'
END

 

It looks so clean compared to the first example! but it wasn’t until I tested the second method out that I realised that the behaviour of the two CASE expressions are different as outlined on books online

The CASE expression has two formats:
The simple CASE expression compares an expression to a set of simple expressions to determine the result.
The searched CASE expression evaluates a set of Boolean expressions to determine the result.
Both formats support an optional ELSE argument.

 

I put together some examples to illustrate the difference when evaluating Null using the two Case expressions, the query returns the column ‘Databasename’ from the derived list values clause, example 1 has a Null value and example 2 has a value of ‘SQLUndercover’ which you will see below:

 

Example 1:

SELECT
Databasename,
CASE Databasename
	WHEN NULL THEN 'It''s a NULL'
	WHEN 'SQLUndercover' THEN 'Match Found'
	ELSE 'No match Found'
END AS CaseExample1,
CASE
	WHEN Databasename IS NULL THEN 'It''s a NULL'
	WHEN Databasename = 'SQLUndercover' THEN 'Match Found'
	ELSE 'No match Found'
END AS CaseExample2
FROM (VALUES(NULL)) AS DerivedList (Databasename)
2018-11-28 14_26_51
Different results when evaluating a NULL

 

 

Example 2:

SELECT
Databasename,
CASE Databasename
	WHEN NULL THEN 'It''s a NULL'
	WHEN 'SQLUndercover' THEN 'Match Found'
	ELSE 'No match Found'
END AS CaseExample1,
CASE
	WHEN Databasename IS NULL THEN 'It''s a NULL'
	WHEN Databasename = 'SQLUndercover' THEN 'Match Found'
	ELSE 'No match Found'
END AS CaseExample2
FROM (VALUES('SQLUndercover')) AS DerivedList (Databasename)
2018-11-28 14_27_02
Same results when evaluating a value

 

Funny thing is I stumbled upon this by accident when I was making some code changes the other day and when I tested the code I wondered why the NULL was not being evaluated correctly! Damn those aesthetics 🙂

 

The interesting part is when you look at the execution plans:

 

2018-11-28 14_45_34.png
Evaluating a NULL using CASE Databasename WHEN NULL THEN…

 

2018-11-28 14_46_02
Evaluating a NULL using CASE WHEN Databasename IS NULL THEN…

 

Surprisingly the NULL appears to be evaluated the same but the returned value is different, let’s see what the plans look like when there is a value to evaluate.

 

2018-11-28 14_47_13
Evaluating a non NULL Value using CASE Databasename WHEN NULL THEN…

 

2018-11-28 14_47_23
Evaluating a non NULL value using CASE WHEN Databasename IS NULL THEN…

 

Ahh interestingly this time there is an ‘IS’ comparison operator used in the plan.

So be careful when evaluating NULL within a CASE expression, be sure to choose the correct type of CASE for the job otherwise you may find your queries returning incorrect data.

I conducted these test using ANSI_NULLS ON, you will find that the behaviour changes if you are using ANSI_NULLS_OFF as the equality comparison of NULL is allowed when it is set to off therefore the results should be the same however be sure to always test it out 🙂

 

Thanks for reading.

5 thoughts on “A curious case of case when and Null values

Add yours

  1. The difference is that the “CASE x WHEN NULL” (simple case expression) performs an equality test, which follows the semantics that a comparison with NULL is always FALSE. The “CASE WHEN x IS NULL” (searched case expression) executes the supplied expression that performs an explicit null check, so it can return the expected value of TRUE when x = NULL. It’s logical when you understand the comparisons that are being used in the different forms of the CASE statement.

    Like

  2. Thanks for this post.
    This is why I generally follow a couple of rules:
    1) Never use nulls. At least don’t let them stand in for an actual values, and
    2) Always be explicit. Don’t be lazy and assume implicit type conversion.

    In this case I’d use the COALESCE or ISNULL function to get a known type.

    Other than this “gotcha” are there any performance difference between the two CASE constructs?

    Like

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

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: