This rather sucks, as the trend seems to be making more and more things zoomable and adjustable and all that. And all in time for me and Sean to grow thoroughly into middle age, with our progressively stronger computer reading glasses.
All this is to say, it’s like some fun, weird, depressing Easter egg hunt to wander around the corridors of Connect.Microsoft.com, the official MS bugs and suggestions site.
So, from Connect: What are YOUR favorite bugs? Most unjustly closed suggestions? More worthless requests? Inquiring minds want to know…
To determine whether an expression is NULL, use IS NULL or IS NOT NULL instead of comparison operators (such as = or !=). Comparison operators return UNKNOWN when either or both arguments are NULL.
Example time – what does != NULL do?
Let’s walk through an example.
This will create a temporary table and add three rows, one of which has a NULL value in the column “mychar”:
CREATE TABLE #tmp (id INT IDENTITY(1,1), mychar CHAR(1) NULL);
INSERT INTO #tmp (mychar) VALUES ('a'), ('b'), (NULL);
SELECT id, mychar FROM #tmp;
So, the data in our table is:
Now it’s very easy to demonstrate how the IS NULL/IS NOT NULL predicates will compare against the =/!= comparison operators.
IS NULL vs =
First, let’s take a look at IS NULL versus = NULL.
select id, mychar FROM #tmp WHERE mychar IS NULL;
select id, mychar FROM #tmp WHERE mychar = NULL;
The IS NULL query returns one row, as it should:
But the “= NULL” query returns no rows. Why? Because SQL has NO IDEA what you’re talking about. “Does mychar equal an unknown? Um. I don’t know? Whatever, here’s your empty result set.”
IS NOT NULL vs !=
And now, IS NOT NULL versus != NULL.
select id, mychar FROM #tmp WHERE mychar IS NOT NULL;
select id, mychar FROM #tmp WHERE mychar != NULL;
The IS NULL query returns two rows, as it should:
But again, the query with the comparison operator “!= NULL” returns no rows. “Does mychar not equal an unknown?” is, as far as SQL Server is concerned, not a valid question. It’s exactly like asking a coworker, “What sized year did you eat for lunch?” or “What color do you want that database?”
A little discussion…
As to whether it’s faster, as someone asked, to use = NULL and != NULL…I haven’t run the benchmarks, as it’s nonsense. But I’d assume these would be faster than IS NULL/IS NOT NULL, since SQL doesn’t bother trying to work out “is this equal to unknown”. You’re just not going to get any rows back.
If you’d like to speed up performance in this way, it’d be much simpler and clearer to use WHERE 1=2; or to not write the query at all.