How SQL Server Treats NULLs In Case Expressions

I Forgot

---

Honest, I did. It happens to the best of us.

Why, just last week I forgot everything for 3 hours.

It was lovely.

These two queries look close enough , but they aren’t.

--Numero Uno
SELECT   CASE v.UserId WHEN NULL 
                       THEN 'NULL!'
                       ELSE 'NOT NULL!'
         END AS Scrubbed,
         v.VoteTypeId,
         COUNT_BIG(*) AS records
FROM     dbo.Votes AS v
GROUP BY CASE v.UserId WHEN NULL 
                       THEN 'NULL!'
                       ELSE 'NOT NULL!'
         END,
         v.VoteTypeId
ORDER BY v.VoteTypeId;

--Numero Dos
SELECT   CASE WHEN v.UserId IS NULL THEN 'NULL!'
              WHEN v.UserId IS NOT NULL THEN 'NOT NULL!'
         END AS Scrubbed,
         v.VoteTypeId,
         COUNT_BIG(*) AS records
FROM     dbo.Votes AS v
GROUP BY CASE WHEN v.UserId IS NULL THEN 'NULL!'
              WHEN v.UserId IS NOT NULL THEN 'NOT NULL!'
         END,
         v.VoteTypeId
ORDER BY v.VoteTypeId;

Results To Gridiot

---

The first query returns what appears to be rather odd results.

There are many NULLs in this column, and they’re exposed when we run the second query.

See them? All the NULLs? They’re here now.

1-900-MUPPET

---

The first query is the same thing as saying UserId = NULL.

That doesn’t get you anywhere at all in a database.

What is this, EXCEL?

To judge the NULL and the NOT NULL, you have to use IS NULL and IS NOT NULL.

But I forgot about that in CASE expressions, so I decided to write it down.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.