What is Handle NULLs

A NULL value means no value or unknown. It does not mean zero or blank, or even an empty string. Those values are not unknown. A NULL value can be used for values that haven’t been supplied yet, for example, when a customer has not yet supplied an email address. As you’ve seen previously, a NULL value can also be returned by some conversion functions if a value is not compatible with the target data type.

You’ll often need to take special steps to deal with NULL. NULL is really a non-value. It is unknown. It isn’t equal to anything, and it’s not unequal to anything. NULL isn’t greater or less than anything. We can’t say anything about what it is, but sometimes we need to work with NULL values. Thankfully, T-SQL provides functions for conversion or replacement of NULL values.

ISNULL

The ISNULL function takes two arguments. The first is an expression we are testing. If the value of that first argument is NULL, the function returns the second argument. If the first expression is not null, it is returned unchanged.

For example, suppose the Sales.Customer table in a database includes a MiddleName column that allows NULL values. When querying this table, rather than returning NULL in the result, you may choose to return a specific value, such as “None”.

SQLCopy

SELECT FirstName,
      ISNULL(MiddleName, 'None') AS MiddleIfAny,
      LastName
FROM Sales.Customer;

The results from this query might look something like this:

FirstName

MiddleIfAny

LastName

Orlando

N.

Gee

Keith

None

Howard

Donna

F.

Gonzales

…

…

…

 Note

The value substituted for NULL must be the same datatype as the expression being evaluated. In the above example, MiddleName is a varchar, so the replacement value could not be numeric. In addition, you’ll need to choose a value that will not appear in the data as a regular value. It can sometimes be difficult to find a value that will never appear in your data.

The previous example handled a NULL value in the source table, but you can use ISNULL with any expression that might return a NULL, including nesting a TRY_CONVERT function within an ISNULL function.