by Greg Specht

Created on: November 11, 2009   Last Updated: May 27, 2010

One of the more confusing aspects of SQL compliant relational database management systems is the concept of a NULL data value.  Most computer programming languages, such as C or C++, set NULL to a numerical value of zero.  This implies that the data field contains some sort of data that may be compared to the numerical value of zero, or NULL.  In an SQL database, if a data field is NULL it means that the field has never been assigned a value of any kind, whether that value is zero or any other value.

This may be best illustrated by comparing the use of data variables in C or C++ to SQL.  In C, when a variable is declared, the memory for that variable is allocated, but not cleared or altered in any way.  The means that the variable may have any value in it at that time.  Therefore, it is common practice to immediately set the value of the variable to zero or NULL (which are equivalent values), before using the variable.

In SQL, when a record is retrieved from a table, there may be some fields in that record that were never assigned a value.  These fields are considered NULL.  Not because they are equal to a value of NULL, but because there is no data value in that field to which anything may be compared.

An example would be a database table of contact information that has fields for three phone numbers, cell phone, home phone, and work phone.  If only the home phone field is used when the database record ins created, the other two phone number fields in that field would be NULL.  If fields in question were to be compared to any value such as zero, (the C/C++ value of NULL) the comparison would return false.

However, if the SQL comparison of isNULL is used on the either or both of the phone number fields that have no data, the comparison would return true.  This is because the fields are in a condition of NULL rather than a value of NULL.

Perhaps the easiest way to remember how to properly handle data in an SQL data field that may or may not have be NULL is to remember that by comparisons that use "=" or "<>" are comparing the data that is currently in that field to the 'value' NULL, while comparisons that use "IS NULL" or "IS NOT NULL" are asking if there has been any value at all assigned to this field including the value of NULL.

Learn more about this author, Greg Specht.
Click here to send this author comments or questions.