Of NULLs and CASEs

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.

dumb party

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

Get’em

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!



One thought on “Of NULLs and CASEs

Leave a Reply

Your email address will not be published. Required fields are marked *