“Thing IS NOT NULL” isn’t the same as “Thing != NULL”

Nonsensical questions: What color do you want that DB?
Nonsensical questions: What color do you want that DB?

I’ve talked about NULLs before. (Ref: Here’s a bit on ISNULL(), and here’s one about understanding NULL at a basic level.)  And yet, it’s worthwhile to bring these things up from time to time.

“[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:

id mychar
1 a
2 b

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:

id mychar

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.


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:

id mychar
1 a
2 b

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.

Happy Days,

Have you checked out Minion Reindex yet? It’s our free, easy, comprehensive index maintenance solution. You’ll love it, seriously.

Sean’s session, “Monster Text Manipulation: Regular Expressions for the DBA” #SQLBits

Update: Thank you, SQLBits, for rescheduling Sean’s session! In addition to the recording/code link for this class (below), you can find recording/code for Stored Procedure Solutions here, and for Powershell Cmdlets for DBAs here.

We had an awful schedule mix up here at SQLBits. If, as a result, you missed out on Sean’s Regular Expressions for the DBA session, you can find a previous recording here, and the session code here.

To the attendees and organizers of SQLBits: this mixup was entirely my doing, and I am VERY sorry for the inconvenience.

Fun-fun-fun in the London sun (and Cardiff, and Exeter!)

O hai UK!
O hai UK!

We’re in London right now for SQLBits, which has been ABSOLUTELY LOVELY so far. If you happen to’ve seen our precon or one of our sessions, please submit feedback!

If you’re anywhere near Cardiff, Wales on Monday 9th, or Exeter, England on Tuesday 10th, then you should hit the user group meeting, as we’ll be speaking at each!