Beware of Null Database Columns

Tips and Hints on
Posted on Sep 23rd, 2014 by Fernando Zamora
No Bugs Allowed

This morning I got bitten by an old and well known bug feature. I only call it a bug because it bites pretty good. When a column is null in a relation database it cannot be implicitly selected. In other words, the record cannot be selected by saying WHERE MY_COLUMN != ‘SOME_VALUE’. It must be selected by explicitly saying WHERE MY_COLUMN IS NULL or in our case WHERE (MY_COLUMN IS NULL OR MY_COLUMN != ‘SOME_VALUE’. Where ‘!=” means not equal. It doesn’t make sense why NULL values get this particular special treatment. Perhap it is because the RDBMS cannot easily compare NULL values to actual values. Or maybe it is too processing intensive. Whatever the reason is, seems that this is a standard practice for all RDBMS’s. So it is something to be aware of. This post on Stack Overflow explains it best:

http://stackoverflow.com/questions/18243755/postgresql-not-ilike-clause-does-not-include-null-string-values

Leave a Reply




Post Comment

Connect With Us

Recent Posts

A Guide for Learning Design Patterns

July 28th 2016 by Fernando Zamora If you’ve been writing code for more than a year, you might have h...

Read More

Using UML to Analyze Legacy Code

June 30th 2016 by Fernando Zamora For every programmer out there creating brand new code and working...

Read More

Python vs. Other Languages

April 29th 2016 by Fernando Zamora For the last two months or so my fellow McLane Advanced Technolog...

Read More

Naming Your Adapter When Implementing the Adapter Pattern

October 19th 2015 by Fernando Zamora At some point in your project you may need to use a third party...

Read More

10 Methods to Help You Debug Legacy Code

September 24th 2015 by Fernando Zamora A large majority of the work we do in our project is to fix r...

Read More