I just recorded a couple of Midnight Snacks on NULLs, and I felt inspired to blog about it too.
Equals Null Mistake (video)
First, let's talk about a mistake that's easy to make, especially if you're in the throes of a coding frenzy, tossing JOINs here and WHERE clauses there, to get your data set returned just right. So you're working with this:
select * from [Hortence]
WHERE heightIn >= 40
AND (lastname LIKE 'M%' OR lastname LIKE 'N%')
And the query returns 32 rows. So you think, ok good, now let's see all of the M% and N% people who have NULL for the heightIn column…
select * from [Hortence]
WHERE heightIn = NULL
AND (lastname LIKE 'M%' OR lastname LIKE 'N%')
Beautiful! And you run the query, and it returns no rows. "Now," you say quietly to yourself, "that's just not right. I know there are several rows with NULL heightIn fields!" So you widen the result set, just to see what's up:
select * from [Hortence]
WHERE heightIn = NULL
And it returns no rows. "That's TOTALLY not right," you say loudly, startling a passing business analyst. "What the friggle?" You try again:
select * from [Hortence]
And there are rows, and yes, some of them have NULL heightIn fields. "Well I KNEW that already!" you shout, and building security starts to get concerned. Slow down, cowpoke, and let's try again. It's really easy to forget that NULLs are a special case. NULL means "unknown"…Null can't equal Null because there's no way to prove that an unknown equals another unknown. So for our special case NULL, we must use our special NULL comparison operator, IS NULL.
select * from [Hortence]
WHERE heightIn IS NULL
AND (lastname LIKE 'M%' OR lastname LIKE 'N%')
There. Now stop talking to yourself at work.
About Nulls
We can have some quick fun with NULLs. Walk through the following code and comments in SSMS for a quick lab on NULL behavior.
IF 1 = 1
SELECT '1 = 1' — true, 1 = 1
IF 1 = NULL
SELECT '1 = NULL' — No, NULL is unknown
IF 1 <> NULL
SELECT '1 <> NULL' — Again, false: nothing cannot be proven equal or inequal to the unknown NULL
IF 1 IS NULL
SELECT '1 IS NULL' — "IS NULL" is the right operator, but the known value 1 clearly isn't NULL (unknown)
IF 1 IS NOT NULL
SELECT '1 IS NOT NULL' — True! 1, a known value, is NOT NULL (unknown)
IF '' IS NULL
SELECT '<blank> IS NULL' — The blank string value '' is not unknown; we know it's blank. So '' IS not NULL
IF '' IS NOT NULL
SELECT '<blank> IS NOT NULL' — See? '' IS NOT NULL. Told you so.
SELECT ISNULL(NULL, 1) — the ISNULL function returns the first non NULL value from a list of two.
SELECT ISNULL(1, 2)
SELECT ISNULL(3,2)
— the COALESCE function returns the first non NULL value from a list of two or more.
SELECT coalesce(NULL, 1, 2, 3, NULL, 4, 5, 'xyz') [coalesce]
-Jen McCown, http://www.midnightdba.com/
Love it. I find it helpful to say that NULL is an indicator, not a value, so don’t compare it to values. Just check whether the value is actually an indicator.
Righty-oh! 🙂