ISNULL in SQL Server (and other NULL functions)

5 min read · Feb 22, 2023

In SQL Server, the ISNULL function is used to replace NULL values with a specified replacement value. In this article, we will explore the ISNULL function and its usage in SQL Server.

The ISNULL function syntax is as follows:

ISNULL ( check_expression , replacement_value )

The first parameter is the expression to be checked for NULL values, and the second parameter is the replacement value. If the check_expression is NULL, the ISNULL function will return the replacement value. If the check_expression is not NULL, the ISNULL function will return the check_expression.

Here’s an example of using the ISNULL function:

SELECT ISNULL(FirstName, 'N/A') AS FirstName, ISNULL(LastName, 'N/A') AS LastName
FROM Employees

In this example, the FirstName and LastName columns from the Employees table are checked for NULL values. If either column contains a NULL value, the ISNULL function will replace it with the string ‘N/A’.

The ISNULL function is often used in conjunction with the SELECT statement to retrieve data from a table. It is also used in the WHERE clause to filter records that contain NULL values. Here’s an example:

SELECT *
FROM Employees
WHERE ISNULL(FirstName, '') = ''

In this example, the WHERE clause checks for records where the FirstName column is NULL or empty. The ISNULL function replaces the NULL value with an empty string (‘’), which is then compared to an empty string. If the FirstName column contains a NULL value or an empty string, the record will be returned.

The ISNULL function can also be used with calculated columns. Here’s an example:

SELECT ProductName, UnitPrice, UnitsInStock,
       ISNULL(UnitPrice * UnitsInStock, 0) AS TotalValue
FROM Products

In this example, the TotalValue column is calculated by multiplying the UnitPrice and UnitsInStock columns. The ISNULL function is used to replace NULL values with 0. If either column contains a NULL value, the TotalValue column will be set to 0.

Another use of the ISNULL function is to concatenate strings that may contain NULL values. Here’s an example:

SELECT ISNULL(FirstName + ' ', '') + ISNULL(LastName, '') AS FullName
FROM Employees

In this example, the FirstName and LastName columns are concatenated to create the FullName column. The ISNULL function is used to replace NULL values with an empty string (‘’). If either column contains a NULL value, the FullName column will still be populated with the non-NULL value.

In addition to the ISNULL function, SQL Server also provides the NULLIF function, which returns NULL if two expressions are equal. Here’s an example:

SELECT NULLIF(FirstName, LastName) AS Result
FROM Employees

In this example, the NULLIF function is used to compare the FirstName and LastName columns. If the two columns are equal, the Result column will be set to NULL. If the two columns are not equal, the Result column will contain the value of the FirstName column.

ISNULL (SQL Server) and NVL (Oracle)

ISNULL and NVL are similar functions used in different relational database management systems (RDBMS) to handle NULL values. ISNULL is used in SQL Server, while NVL is used in Oracle.

ISNULL and NVL both take two parameters. The first parameter is the expression to check for NULL values, and the second parameter is the value to replace the NULL values. If the first parameter is NULL, the second parameter is returned. If the first parameter is not NULL, the first parameter is returned.

Here’s an example of using the NVL function:

SELECT NVL(FirstName, 'N/A') AS FirstName, NVL(LastName, 'N/A') AS LastName
FROM Employees;

This query retrieves the FirstName and LastName columns from the Employees table. If either column contains a NULL value, the NVL function will replace it with the string ‘N/A’.

The syntax of the ISNULL function is slightly different:

SELECT ISNULL(FirstName, 'N/A') AS FirstName, ISNULL(LastName, 'N/A') AS LastName
FROM Employees;

In this query, the ISNULL function is used to achieve the same result as the NVL function.

It’s important to note that while ISNULL and NVL are similar functions, they are not interchangeable. If you’re working with Oracle, you must use NVL. If you’re working with SQL Server, you must use ISNULL.

Here’s an example of using the ISNULL function in a WHERE clause:

SELECT *
FROM Employees
WHERE ISNULL(FirstName, '') = '';

In this query, the WHERE clause checks for records where the FirstName column is NULL or empty. The ISNULL function replaces the NULL value with an empty string (‘’), which is then compared to an empty string. If the FirstName column contains a NULL value or an empty string, the record will be returned.

Here’s an example of using the NVL function in a WHERE clause:

SELECT *
FROM Employees
WHERE NVL(FirstName, '') = '';

This query uses the NVL function to achieve the same result as the previous query that used the ISNULL function.

Most Commonly Used NULL functions in SQL

Here’s a table comparing some of the most commonly used NULL functions in different relational database management systems (RDBMS):

Here’s a brief explanation of each function:

• ISNULL: Returns the second parameter if the first parameter is NULL, otherwise returns the first parameter. Available in SQL Server.
• NULLIF: Returns NULL if the two parameters are equal, otherwise returns the first parameter. Available in SQL Server, Oracle, and MySQL.
• NVL: Returns the second parameter if the first parameter is NULL, otherwise returns the first parameter. Available in Oracle.
• COALESCE: Returns the first non-NULL parameter. If all parameters are NULL, returns NULL. Available in SQL Server, Oracle, and MySQL.
• IFNULL: Returns the second parameter if the first parameter is NULL, otherwise returns the first parameter. Available in MySQL.
• CASE WHEN NULL THEN: Allows you to specify a default value when a column is NULL. Available in SQL Server, Oracle, and MySQL.

Conclusion

the ISNULL function is a useful tool for replacing NULL values in SQL Server. It can be used in conjunction with the SELECT statement, the WHERE clause, and calculated columns. It can also be used to concatenate strings that may contain NULL values. By using the ISNULL function, you can ensure that your data is accurate and consistent, and that your queries return the expected results.