Do You Want to Learn SQL?

Take our free eight day course, where I'll teach you in simple to understand English all you need to get started learning SQL.

Start Now
-
DAYS
-
HOURS
-
MINUTES
-
SECONDS

Free Beginners SQL Course!

Click Here!
Tutorial

SQL ISNULL Function (Transact SQL)

Kris Wenzel

·

·

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

Table Of Contents
  1. Description
  2. SQL ISNULL Usage Notes
  3. Syntax
  4. SQL ISNULL Examples
  5. See Also
More from the blog

MySQL PostgreSQL SQLite SQL Server