These are not the NULLs you are looking for…

Just like Obi-Wan Kenobi messing with minds using the force, allowing null values in database fields can lead to undesired behavior.  Null values really aren’t a ‘value’…they are an absence of value.  They act more like a field state than a referenceable value.  This can lead to interesting behavior when querying a field based on value.

For example, consider the following table:

ID
field
Value
0
foo
this is a foo test
1
[null]
null test
2
boo
this is a boo test
3
goo
this is a goo test
4
[null]
another null test

If you issue a query like this:

Select * from table where field != ‘foo'

One would normally look at this and think, “I’m going to get all the rows where field is not equal to ‘foo’”.  What you are really going to get is all the rows where field is not null and the value of field is not equal to foo. In other words, this is your result:

ID
field
Value
2
boo
this is a boo test
3
goo
this is a goo test

Which may or may not be what you are expecting.  Because you reference a value for field in the where clause, it means that all of the values of foo that are not a value (i.e. null) will not be included in the query.  If you were expecting those values to be in the result set you have to explicitly reference them as shown in the following query:

Select * from table where (field is null or field != ‘foo’)

This query will return the result:
ID
field
Value
1
[null]
null test
2
boo
this is a boo test
3
goo
this is a goo test
4
[null]
another null test

Depending on what you were expecting there is a wide variance between the two result sets.  When creating database tables one must take great care to decide if NULL should be allowed and if not allowed then what the default value should be.  When querying a table that has fields that allow NULL values one must take care of how they reference that value in the query to ensure that the right response is returned.

Null values can be a valuable characteristic when referencing data but they can have undesirable consequences when not use appropriately.

Leave a Reply

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