Jul 11, 2007 / sql
SELECT 'Aaaargh' WHERE 'PAIN' IN (NULL)

Today I got really confused by some unexpected NULL related behaviour in SQL Server 2005. It really threw me to the point where I was wandering if there was a bug, but of course it was human error.

A quiz for you...

In SQL Server 2005, what will this query return?

select 'Fee' as Result where 1 not in (2,3)

The answer, of course, is this...

Result
------
Fee

...because 1 is not in the set (2,3).

And what about this one, is it the same?

select 'Fo' as Result where 1 not in (null,2,3)

I expected it would be the same as above. But no no, it is actually this:

Result
-------

I must admit this toally threw me, I've been writing SQL for a long time and I don't ever recall being caught out by this problem. What's happening here is that a null is neither equal or unequal to anything, therefore when we ask if 1 is NOT in (null,2,3), the database can't say that's strictly true

The fact that I missed this got me worried - I think I've been using SQL a lot less recently because I use OR/M tools for 90% of my data access code, so I'd forgotten about the whole tristate logic thing where it's necessary to put explicit null checks in. Drat. It's really true that, if you don't practice your skills, they fade.

Going back to the above query, you probably wouldn't usually have that, a better example is something like this...

*find customers who aren't competition winners*
select * from customers where id not in ( select winner_id from competitions )

If any of the competitions has a winner_id of NULL, then the whole query returns an empty set even if there are loads of customers who aren't competition winners. Alternatively, this would get the desired result:

select * from customers where id not in ( select winner_id from competitions where winner_id is not null)

or this

select * from customers where id not exists ( select 'x' from competitions where winner_id = customers.id )

What about this one?

select 'Fi' as Result where 1 not in (1,2,3)

Again, easy. The answer is this

Result
-------

Another interesting one...

select case when null = null then 'Yup' else 'Nope' end as Result

Gives

Result
------
Nope

And this....

select case when null is null then 'Yup' else 'Nope' end as Result

Gives...

Result
------
Yup

Think I'm off to read up on my tristate logic rules again :(


You may also like...