Random Bits & Bytes

I have found after much annoyance, that an SQL query I had in production was incorrectly checking for NULL in my SQL statement.

This is a simple school-boy error that I will now never do again.

The issue is that if you have a case statement as follows:

CASE WHEN <Value to check> = NULL
    THEN <Value to replace NULL>
    ELSE <Value that is not null>
END

This will always return NULL when there is a NULL value that is in the value check.

However, all you need to do is change the ‘=’ to an ‘IS’ and it will calculate correctly

CASE WHEN <Value to check> IS NULL
    THEN <Value to replace NULL>
    ELSE <Value that is not null>
END