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)

 

 

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)

 

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:

 

 

 

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.

 

 

 

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.