Functions to Work with NULL Values

Eric Vaillancourt — Thu, 03 Apr 2008 04:25:00 GMT

Today I was asked what was the replacement for the IIF command in MS Access and how to manage nulls in SQL Server. So I decided to write about nulls, not that I like to work with them...but sometimes we don't have a choice ;)

SQL Server may generate unexpected results when performing calculations on columns that have NULL values. So, to resolve this problem you need to use the ISNULL, NULLIF, and COALESCE functions of SQL Server 2005 when your columns hold NULL values.

ISNULL: The ISNULL function replaces NULL values with the specified replacement value. The following is the syntax of the ISNULL function:

ISNULL ( check_expression , replacement_value )

The function takes the following arguments:

check_expression is the expression to be checked for NULL values and can be of any data type.

replacement_value is the expression to be returned if check_expression is NULL and should be of the same data type as the check_expression.

The following code example demonstrates the use of the ISNULL function in a SELECT statement.

USE Adventureworks;

SELECT AVG(ISNULL(Weight, 60))

FROM Production.Product

In this example, the ISNULL function is used to find the average of the weight of all products. The ISNULL function substitutes the value 60 for all NULL entries in the Weight column of the Product table.

NULLIF: The NULLIF function returns a NULL value if the two specified expressions are equivalent. The following is the syntax of the NULLIF function:

NULLIF (expr , expr)

The function takes two arguments. The argument, expression, can be a constant, column name, function, subquery, or any combination of arithmetic, bitwise, and string operators. The NULLIF function returns the result that is of the same data type as the first expression.

The NULLIF function returns the first expression if the two expressions are not equivalent. Otherwise, it returns a NULL value.

The following code example retrieves the average StandardCost of products that belong to the Class ‘M’.

USE Adventureworks;

SELECT AVG (StandardCost) AS AvgCost

FROM Production.Product

WHERE Class='M'

This query returns an average of 378.5403 that includes some rows that have NULL values in the StandardCost column. Therefore, the average cost value that is displayed in the result set is incorrect, it should have been 402.199. However, the same query can be rewritten with the help of the NULLIF function to ensure that the products with NULL as StandardCost are not included in the calculation.

USE Adventureworks;

SELECT AVG(NULLIF(StandardCost, 0)) AS AvgCost

FROM Production.Product

WHERE Class='M'

COALESCE: The COALESCE function returns the first non-null expression among its arguments. The following is the syntax of the COALESCE function:

COALESCE ( expression [ ,...n ] )

The function takes two arguments. The argument expression is an expression of any data type. The argument n is a placeholder indicating that multiple expressions can be specified. All expressions must be of the same data type.

The COALESCE function returns the result that is of the same data type as the expression. If all arguments of the COALESCE function are NULL, then it returns NULL.

The ISNULL function replaces NULL values with the specified replacement value. The following is the syntax of the ISNULL function:

The following example uses the COALESCE function to return the first non-null telephone number for a particular customer and for customers with no valid telephone numbers, the string 'No number on file' is displayed.

SELECT CustomerName, COALESCE(CellPhone, OfficePhone, HomePhone, 'No number on file') as Telephone FROM Customer

Hope this helps,

Eric Vaillancourt

http://www.sqlprof.com

Technorati : COALESCE,NULLIF,ISNULL

MS SQL Dev : Aggregate, Coalesce

Functions to Work with NULL Values