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.
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.
ISNULL (input_expression, replacement_value)
SQL ISNULL Examples
The following two examples are a simple demonstration of how an ISNULL function works.
/* Answer */ SELECT ISNULL('Not NULL', 'Replace with me') 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.
/* 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.
/* 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.
/* 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.