Happy ‘NULL’ Year


SQL Server has provided support for NULL values in table columns for… well just about forever.  A NULL value is not really a specific value through.  Rather it indicates the absence of a value.  More to the point, one NULL is not equal to another NULL.  If two values are unknown, how can you say that they are equal or even that they are not equal to each other?  In fact, the result of comparing two NULLs must logically be a NULL indicating that the result of the comparison is unknown.

Why go through all this discussion about nothing?  Well actually, it is not nothing because nothing is a specific value also known as ‘0’ for numbers and an empty string for string variables.  NULL is not a specific value as I said before.  So you might say that a discussion about NULLs is a discussion about something that you don’t know.

Confused?  Ok, enough of that.  Suppose you had a table of customers that included their address information.  You have fields for the customer’s street address, city, state, and country.  Unfortunately, not every country has states.  They may have provinces, districts, regions, or perhaps nothing at all.  For that reason, the table designer for the customer table decided to allow NULL values for the state column so that it could be ignored in countries that it does not apply.

This method sounded great at first.  In fact, for simple queries to list or count customers by state, you could use a simple query like the following to get all customers from the state of Florida in the United States.

SELECT custname, address, city, state, country
FROM Customer
WHERE state = ‘FL’

This statement would work great.  It would work even better perhaps if you had an index on the state column, but that’s another discussion.  But what if you wanted to know all the customers from United States in which the state was not entered.  You may be tempted to write the following query.

SELECT custname, address, city, state, country
FROM Customer
WHERE COUNTRY = ‘United States’
AND state = NULL

Notice in this SQL statement that the second Where clause tries to compare the column value in State to the NULL constant.  Unfortunately, this will not work because you cannot compare one NULL value to another without resulting in a NULL as discussed earlier.  By resulting in a NULL value for the comparison, even the records you want to see will not be returned by this query.  You might even triy the following statement to return customers not in Florida but in the United States.

SELECT custname, address, city, state, country
FROM Customer
WHERE COUNTRY = ‘United States’
AND state <> ‘FL’

Again because NULL values are strictly speaking unknown, any record in which the value for state was NULL would return a NULL value for the Boolean expression: state <> ‘FL’ and therefore not be returned by the query.  Sure you would get all of the customers from other states in the United States, but you would not get any records where the value for state was omitted.  This might lead you to believe that all United States customers had properly identified states, but you would be wrong.

So how do you filter on NULL values?  You must use a query like the following:

SELECT custname, address, city, state, country
FROM Customer
WHERE COUNTRY = ‘United States’
AND state IS NULL

In this expression, the SQL statement is not attempting to compare the value of state to a NULL value but rather is just checking if the value of state is NULL.  This seems like a subtle difference at first, but it is extremely important.

So when can you use the NULL constant if not in queries?  Think of the NULL constant as a way to set a value for a column in a table to NULL as in the following:

UPDATE customer
SET state = NULL
WHERE country = ‘Bahamas’

This statement sets the value of the column state to NULL for all customer records from the Bahamas.  Is that a specific value?  No, it merely indicates that the value is undefined.

After the holidays, I may come back to look at how to improved the performance of your SQL queries and some of the idiosyncrasies that you may encounter.  For example, how you compare dates depends on the data type of the date field.

C’ya next year.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s