SQL Server: NULL plus NULL = NULL

In most SQL databases you are likely to have columns that have a NULL value. In SQL a NULL value means unknown. For example if you new customer sign up but they don’t have a extension for their phone number then a NULL value will be stored in the extension column.

So you’ll end up with a record that looks like this:

FirstName	LastName	PhoneNumber	Extension
Jane	Smith	555-555-5555	NULL

Because a NULL value is unknown interesting things tend to happen when you combine a non-NULL value with a NULL value.

If you run the following SQL statement against a table that has the data above in it you will get a NULL value for the full phone number.

SELECT FirstName + LastName As CustomerName, PhoneNumber + ‘  Ext  ‘ + Extension AS FullPhoneNumber
FROM Customers

Note: For simplicity I’m assuming that the values in the PhoneNumber and Extension columns are both strings.

This would be the results of that query:

CustomerName	FullPhoneNumber
Jane Smith	NULL

The reason why the results is NULL is because the value in the PhoneNumber column (555-555-5555) was combined with the value in the Extension column which is unknown (NULL) which results in unknown (NULL).

In order to fix the query to return a value for FullPhoneNumber you would have to use the ISNULL function to change the value for extension column from NULL to something else.

SELECT FirstName + LastName As CustomerName, PhoneNumber + ‘  Ext  ‘ + ISNULL(Extension,”) AS FullPhoneNumber
FROM Customers

This would be the results of the above query:

CustomerName	FullPhoneNumber
Jane Smith	555-555-5555 Ext

As you may have noticed we are still displaying the Ext statement when there is no extension which looks bad. To fix that issue you could rewrite the statement as below to remove the Ext from the results.

SELECT FirstName + LastName As CustomerName, PhoneNumber + CASE WHEN Extension IS NOT NULL THEN ‘ Ext ‘ + Extension END AS FullPhoneNumber
FROM Customers

This would be the results of the above query:

CustomerName	FullPhoneNumber
Jane Smith	555-555-5555

You could also store a ” value in the field to show that the customer doesn’t have an extension which tends to be a little better because you are stating that you know the customer doesn’t have an extension instead of we don’t know if the customer has an extension. While this maybe better I’ve rarely seen ” stored in a column like this at the companies I’ve worked for. Most of the time developers will store a NULL value in the column.