Selecting from a column with possible NULLs in T-SQL

I write blog posts for different reasons. Sometimes it’s because things aren’t well documented elsewhere, sometimes it’s new and interesting and sometimes it’s purely for my own amusement. Occasionally I write things as I kind of penance – things I should know about but bizarrely forgot or misunderstood. This is one of those. It’s how to select from a column with possible NULLs and not omit expected rows from the results set. This has been documented at length elsewhere but that’s not the point of penance.

Test Cases – equals operator

Working through some examples when comparing NULLs with the equals operator.

NULL is equal to ‘test’

False: as expected

NULL is not equal ‘test’

False:  less expected

NULL is not equal to NULL

False: more expected

NULL is equal to NULL

False: odd again

NULL isn’t equal to anything including itself when using the equals operator. I know this really and we see the same kind of thing in JavaScript.

Test cases – IS NULL

Of course it well known that equals doesn’t do the job with NULLS. The IS NULL operator is correct

Null is null

True.

Null is not null

False.

All as expected.

Test case -selecting from a column with possible NULLs

So this is what tripped me up – when you have a column that is nullable and trying to filter as a string.

I naively thought that there are 2 rows that aren’t ‘Test’ so we will get those. But since NULL is not equal to ‘Test’ and not not equal to ‘Test’ then actually we get one less row than we expected

The correct select is

Which converts the NULL to an empty string so then works

Also works and is the same. Penitential blog post over.

Useful Links

https://stackoverflow.com/questions/18828641/sql-difference-between-coalesce-and-isnull
Comparison of ISNULL and COALESCE

http://adripofjavascript.com/blog/drips/equals-equals-null-in-javascript.html
It’s not just T-SQL. Special handling around NULLs in JavaScript

Leave a Reply

Your email address will not be published. Required fields are marked *