June 4, 2021

Use the SQL ISNULL function to evaluate the input expression for a NULL value. If it is NULL, then ISNULL replaces it with another specified value.

Description

The ISNULL function helps to deal with the NULL values in database records. If we want to view a column with NULL values, we can handle them using the ISNULL function. The function replaces and returns the NULL expression with another value specified as an input argument. If the value is not NULL, then it returns as it is. ISNULL is similar to the SQL COALESCE function.

SQL ISNULL Usage Notes

The first argument of the ISNULL function is the input expression that needs to be evaluated for a NULL value.

The second argument is the replacement value. The ISNULL value returns this specified value if the input expression is NULL.

ISNULL function also evaluates the data types of both input expression and replacement value. If the data types are different, then the ISNULL implicitly converts the data type of the replacement value to the input expression data type. Therefore, the replacement value should be implicitly convertible to the data type of the input expression.

Syntax

ISNULL (input_expression, replacement_value)

SQL ISNULL Examples

The following two examples are a simple demonstration of how an ISNULL function works.

SELECT ISNULL('Not NULL', 'Replace with me') result;
/* Answer */
SELECT ISNULL('Not NULL', 'Replace with me') result;
SELECT ISNULL(NULL, 'Input Expression is NULL') Result;
/* Answer */
SELECT ISNULL(NULL, 'Input Expression is NULL') Result;

In the next examples, we will use the AdventureWorks2019 database.

The following query selects special offers from the Special Offer table by their ID. The maximum quantity shows the maximum discount allowed on an offer.

SELECT SpecialOfferID, ISNULL(MaxQty, 80) MaximumQty FROM Sales.SpecialOffer;
/* Answer */
SELECT SpecialOfferID,
       ISNULL(MaxQty, 80) MaximumQty
FROM   Sales.SpecialOffer;

The ISNULL function replaces all the null values in the column with the specified maximum value of 80.

In the following SQL query, we are using ISNULL with MAX aggregate function.

SELECT MAX(ISNULL(SalesQuota, 80)) MaxSalesQuota FROM Sales.SalesPerson
/* Answer */
SELECT MAX(ISNULL(SalesQuota, 80)) MaxSalesQuota
FROM Sales.SalesPerson

We display the maximum sales quota, that is, maximum sales in a year by salespeople. The ISNULL function replaces NULL sales by 80. The MAX function then selects the maximum value in the SalesQuota column of the SalesPerson record.

ISNULL function can also be used with a where clause in SQL query.

SELECT Name, Color FROM Production.Product WHERE Color IS NULL;
/* Answer */
SELECT Name, Color  
FROM Production.Product  
WHERE Color IS NULL;

The above query returns all the products with NULL values in the Color column. Note, with where clause we use IS NULL with space.

See Also

About the author 

Kris Wenzel

Kris Wenzel has been working with databases over the past 30 years as a developer, analyst, and DBA. He has a BSE in Computer Engineering from the University of Michigan and a MBA from the University of Notre Dame. Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Are YOU READY TO START LEARNING SQL SERVER, BUT DON'T KNOW WHERE TO START?

Nothing is worse than, being excited to learn a new tool but not knowing where to start, wasting time learning the wrong features, and being overwhelmed .

But it doesn't have to be this way.

I'm Putting together a free email course to help you get started learning SQL Server.

Name*
Email*
>