“[Thing] != NULL” is nonsense. It won’t work the way you want it to work, because NULL literally means “unknown”. Nothing in the world can be equal to, or not equal to, an unknown.
The TechNet article on IS [NOT] NULL says:
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.
Have you checked out Minion Reindex yet? It’s our free, easy, comprehensive index maintenance solution. You’ll love it, seriously.