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’

SELECT CASE WHEN NULL = 'test' THEN 'true' ELSE 'false' END

False: as expected

NULL is not equal ‘test’

SELECT CASE WHEN NULL <> 'test' THEN 'true' ELSE 'false' END

False:  less expected

NULL is not equal to NULL

SELECT CASE WHEN NULL <> NULL THEN 'true' ELSE 'false' END

False: more expected

NULL is equal to NULL

SELECT CASE WHEN NULL = NULL THEN 'true' ELSE 'false' END

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

SELECT CASE WHEN NULL IS NULL THEN 'true' ELSE 'false' END

True.

Null is not null

SELECT CASE WHEN NULL IS NOT NULL THEN 'true' ELSE 'false' END

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.

DECLARE @TestTable TABLE (Id INT NOT NULL, Comment VARCHAR(100))
INSERT INTO @TestTable (Id, Comment) VALUES (1, 'Test')
INSERT INTO @TestTable (Id, Comment) VALUES (2, 'Other Value')
INSERT INTO @TestTable (Id, Comment) VALUES (3,  NULL)

SELECT * FROM @TestTable WHERE Comment <> 'Test'

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

DECLARE @TestTable TABLE (Id INT NOT NULL, Comment VARCHAR(100))
INSERT INTO @TestTable (Id, Comment) VALUES (1, 'Test')
INSERT INTO @TestTable (Id, Comment) VALUES (2, 'Other Value')
INSERT INTO @TestTable (Id, Comment) VALUES (3,  NULL)

SELECT * FROM @TestTable WHERE COALESCE(Comment, '') <> 'Test'

Which converts the NULL to an empty string so then works

SELECT * FROM @TestTable WHERE ISNULL(Comment, '') <> 'Test'

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